Reputation: 465
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
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
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