4

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...

6
  • Use the column, ccs_pop, not the alias (pop). Nov 6, 2015 at 12:15
  • ok, but, I gave a simple example, what if ccs_pop is a complex sum within a select, etc
    – WDrgn
    Nov 6, 2015 at 12:16
  • Use the complex expression then. Or write the query you have problem, and we can answer with other alternatives. Nov 6, 2015 at 12:17
  • a cannot do that - it's too complex and it's used in many other sentences/etc
    – WDrgn
    Nov 6, 2015 at 12:25
  • I search for a solution before I make a view with "pop" and I use another view to use the sum in pop...
    – WDrgn
    Nov 6, 2015 at 12:27

1 Answer 1

7

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

select  
    (<insanely complex expression>) 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  
    (<insanely complex expression>) as pop,
    case 
        when <insanely complex expression> is null then 'isnull'
    end
from rap4 ;

or use a derived table:

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

or use a common table expression (CTE):

with cte as
  ( select  
        (<insanely complex expression>) as pop
    from rap4
 )
select
    pop,
    case 
        when pop is null then 'isnull'
    end
from cte ;
1
  • I used the common table expression (CTE), thanks!
    – WDrgn
    Nov 6, 2015 at 12:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.