Using an alias when using the case argument in Oracle SQL

Question

I’m trying to find some time if it is possible to use the alias specified earlier in the select statement, if it can be used in the case later in the case argument for Oracle SQL. Most of the results that I find are on how to create an alias based on a case case, which is not the same problem. A quick example would be something like this:

Select TABLEA.SomeIDNumber AS "Id",
       case ID
          when 3
          then 'foo'
          else 'bar'
       end AS "Results"
FROM OMEGA.TABLEA

It really is not so simple in the SQL expression that I create (it is actually created based on the previous case statement and requires some joins in different tables in order to perform all other corrections of the query, but in fact it would not make sense without knowing more of the base data that I can’t share).

I'm just wondering if you can use the alias in the case argument later in the select statement for Oracle (I know that such things can be done with Access kinda "SQL"). Or is it better for me to do some refinement of the choice to make it a nested election? Probably doable, a little painful.

Answer 1

No, you cannot refer to an alias elsewhere at the same level selectas in the sentence order by, because Oracle assigns it internally.

From the documentation (highlighted by me):

, c_alias, , . . order by, .

, :

select "Id",
    case "Id"
        when 3
        then 'foo'
        else 'bar'
    end AS "Results"
from (
    select TABLEA.SomeIDNumber AS "Id",
    from TABLEA
);