Oracle 12c – Fixing ORA-00904 Invalid Identifier Error with Alias

aliasidentifieroracle-12c

I'm trying to return a product of two subqueries, and thought I'd make life easier by aliasing each of the subqueries, then divide the two aliases to get the number I'm after, like this:

select distinct
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest
where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) as Ha_IN_RKB,
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest) as Ha_total,
ROUND( (Ha_IN_RKB / Ha_total) ,3)
from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest

but this results in a 00904. 00000 – "%s: invalid identifier" error (Oracle doesn't like Ha_IN_RKB or Ha_total ).

Re-writing the query as follows works, but is rather bulky:
select distinct
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest
where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) Ha_IN_RKB,
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest) Ha_total,
ROUND( ((select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest
where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) /
(select SUM(pest.AREA_HA) from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest)) ,3) as RKB_pct
from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest

Anybody know why I can't just make this ROUND( (Ha_IN_RKB / Ha_total) ,3) work ?

Best Answer

Aliases cannot be used directly. You have to abstract them out using a nested subquery or derived table.

with t as (
    select
    (select SUM(pest.AREA_HA) 
       from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest
      where ORG_UNIT_NO IN (1904, 1830,1831, 1902)) 
         as Ha_IN_RKB, 
    (select SUM(pest.AREA_HA) 
       from WHSE_FOREST_VEGETATION.PEST_INFESTATION_POLY pest) 
         as Ha_total
    from dual)
select t.ha_in_rkb, t.ha_total, ROUND(t.Ha_IN_RKB/t.Ha_total,3) from t;
Related Question