Using Alias In When Portion of a Case Statement in Oracle SQL -

i've been trying awhile if it's possible use alias stated earlier in select statement if can used in case later in case statement oracle sql. results find how make alias based on case statement isn't same problem. quick example like:

select tablea.someidnumber "id",        case id           when 3           'foo'           else 'bar'        end "results" omega.tablea 

it's not simple in sql statement i'm creating (it's created based on previous case statement , requires joins on various tables full other pats of query, wouldn't make sense without knowing more of database can't share).

i'm wondering if it's possible use alias in case statement later in select statement oracle (i know such things can done access kinda "sql"). or better me reworking of select make nested select statements? doable, bit more of pain.

no, can't refer alias elsewhere in same level of select, other in order by clause, because of when oracle assigns internally.

from documentation (emphasis added):

you can use column alias, c_alias, label preceding expression in select list column displayed new heading. alias renames select list item duration of query. the alias can used in order by clause, not other clauses in query.

you need use inner query, like:

select "id",     case "id"         when 3         'foo'         else 'bar'     end "results" (     select tablea.someidnumber "id",     tablea );