Use column number from select clause in where clause. Extracting allias original name

Question

So lets say i have a query like this

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

Is it possible instead of a=1 to type something like SELECTED.1 = 1 or to somehow extract allias original name since d=1 doesn't work

Answer 1

It's not possible to do this because of internal complexities about when the WHERE clause gets evaluated. But if the thing you are aliasing is a long expression that you'd rather not repeat, there is a typical solution to this. From https://forums.oracle.com/forums/thread.jspa?threadID=1107532:

The standard solution to this is, you move the query into an inline view (without the where-clause predicate), and then add the where-clause predicate, using the alias, in the outer 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 your example case, that would be:

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

But of course, this wouldn't make sense in your literal example. If the thing you are aliasing is just a column name, then just use the actual column name in the WHERE, no real drawback in that case.

Also note that if you do use this method, you should put as much of the WHERE clause as you can in the internal query (meaning the parts that don't reference an aliased column) to limit the size of the resulting aliased table. For example, if you also wanted to test on b in your example, that 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) e.g.

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