CODEWITHSUNDEEP
sqloracle-database
acolls_badger
acolls_badger

Reputation: 465

Operate on pseudocolumn in oracle

I'm creating two pseudocolumns that I wish to operate on (take the percentage difference of them), as can usually be done with two numeric fields. However I am getting an error;

ORA-00904: "pseudocolumn_B": invalid identifier

My code:

select DW.mykey
     , sum(case
             when ms.day_date BETWEEN TO_DATE('10/09/2014', 'DD/MM/YYYY') AND TO_DATE('07/10/2014', 'DD/MM/YYYY')
               then ms.numbers
             else 0
           end) pseudocolumn_A
     , sum(case
             when ms.day_date BETWEEN TO_DATE('03/08/2014', 'DD/MM/YYYY') AND TO_DATE('09/09/2014', 'DD/MM/YYYY')
               then ms.numbers
             else 0
           end) pseudocolumn_B
      (pseudocolumn_A - pseudocolumn_B)/pseudocolumn_B as change_in_pseudo_columns --This is the line that fails.
from table_1 DW
     join table_2 ms
       on ms.mykey = DW.mykey
    WHERE dw.some_field = 'some_value'
group by DW.mykey
order by DW.mykey

I believe I have been able to do this before with non-oracle databases and as such this is some difference for Oracle, but I may be wrong. How can I operate on pseudo-columns as attempted above?

Upvotes: 0

Views: 188

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

You cannot use the column alias as a column name in the same query. Use it as sub-query.

Put the following, psuedocolumn_A - psuedocolumn_B)/psuedocolumn_B as change_in_psuedo_columns as an outer query :

select psuedocolumn_A - psuedocolumn_B)/psuedocolumn_B as change_in_psuedo_columns 
from (your query in original post)

Or, even an INLINE VIEW would suffice.

Upvotes: 1

radar
radar

Reputation: 13425

You can't use the alias column in the same SELECT statement and also in WHERE clause

you can use a cte and get the required aggregation columns and then do further calculation on them like below

with cte
as
(
select DW.mykey
     , sum(case
             when ms.day_date BETWEEN TO_DATE('10/09/2014', 'DD/MM/YYYY') AND TO_DATE('07/10/2014', 'DD/MM/YYYY')
               then ms.numbers
             else 0
           end) psuedocolumn_A
     , sum(case
             when ms.day_date BETWEEN TO_DATE('03/08/2014', 'DD/MM/YYYY') AND TO_DATE('09/09/2014', 'DD/MM/YYYY')
               then ms.numbers
             else 0
           end) psuedocolumn_B
from table_1 DW
     join table_2 ms
       on ms.mykey = DW.mykey
    WHERE dw.some_field = 'some_value'
group by DW.mykey
)
select * ,
      (psuedocolumn_A - psuedocolumn_B)/psuedocolumn_B 
from cte
order by cte.mykey

Upvotes: 3

Related Questions