Use the column number from the select clause in the where section. Extract the original alias name

Question

So let's say I have such a request

SELECT a as d,b,c FROM myTable WHERE a=1; 

Is it possible instead of a = 1 to print something like SELECTED.1 = 1 or somehow extract the original name allias, since d = 1 does not work

Answer 1

This is not possible due to internal complexity when evaluating a WHERE clause. But if the thing you impose is a long expression that you would prefer not to repeat, there is a typical solution to this. From https://forums.oracle.com/forums/thread.jspa?threadID=1107532 :

The standard solution to this question is: you move the query into a string (without the where-clause predicate), and then add where-clause using the alias in the external query.

So something like this:

 select ... from (select ... here complex expression that is aliased ... from ... where ) A where ... here condition that uses the A.alias column ... 

In the example of your example, this would be:

 SELECT d, b, c FROM ( SELECT a AS d, b, c FROM myTable ) AS myAliasedTable WHERE d = 1 

But, of course, that would not make sense in your literal example. If what you call an alias is just a column name, then just use the actual column name in WHERE, and in this case there is no real flaw.

Also note that if you use this method, you must place as many WHERE clauses as you can in the internal query (which means parts that do not refer to columns with an alias) in order to limit the size of the resulting alias Table. For example, if you also wanted to test on b in your example, this would be:

 SELECT d, b, c FROM ( SELECT a AS d, b, c FROM myTable WHERE b = 1 ) AS myAliasedTable WHERE d = 1 

Answer 2

Use a common table expression (CTE) , for example.

 WITH T AS ( SELECT a as d, b, c FROM myTable ) SELECT * FROM T WHERE d = 1;