Sample Header Ad - 728x90

ORA-00904 invalid identifer error when using alias

0 votes
1 answer
1805 views
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 ?
Asked by grego (101 rep)
Mar 19, 2020, 12:48 AM
Last activity: Mar 19, 2020, 01:39 AM