snippetMinor

how can I use a CASE with an alias defined befor case?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

casecanbeforwithdefinedhowusealias

Problem

how can I use an alias like pop in a further case like in this example (Oracle database).

select  
   (select sum(ccs_pop) from rap4) as pop,
case 
    when pop+x=a+b+c then pop+x
end as sum1,
case 
    when pop+y=d+e+f then pop+y
end as sum2
from rap4

The ideea is that I need "pop" in a complex situation and I need "pop" in many sentences and I need to use an alias or something...

Solution

OK, I'll take the provided example, and modify it a bit:

select  
    () as pop,
    case 
        when pop is null then 'isnull'
    end
from rap4 ;

The above will of course produce an error because an alias defined in the SELECT list, cannot be used in another expression in the same SELECT list or the (same level) WHERE or GROUP BY clauses.

What you can do however is either duplicate the expression:

select  
    () as pop,
    case 
        when  is null then 'isnull'
    end
from rap4 ;

or use a derived table:

select
    pop,
    case 
        when pop is null then 'isnull'
    end
from 
    ( select  
          () as pop
      from rap4
    ) t ;

or use a common table expression (CTE):

with cte as
  ( select  
        () as pop
    from rap4
 )
select
    pop,
    case 
        when pop is null then 'isnull'
    end
from cte ;

Code Snippets

select  
    (<insanely complex expression>) as pop,
    case 
        when pop is null then 'isnull'
    end
from rap4 ;

select  
    (<insanely complex expression>) as pop,
    case 
        when <insanely complex expression> is null then 'isnull'
    end
from rap4 ;

select
    pop,
    case 
        when pop is null then 'isnull'
    end
from 
    ( select  
          (<insanely complex expression>) as pop
      from rap4
    ) t ;

with cte as
  ( select  
        (<insanely complex expression>) as pop
    from rap4
 )
select
    pop,
    case 
        when pop is null then 'isnull'
    end
from cte ;

Context

StackExchange Database Administrators Q#120301, answer score: 7

Revisions (0)

No revisions yet.