0

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 ?

1

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;
| improve this answer | |
  • Excellent, thanks! In this case, would it be accurate to say that you've used the derived table approach, i.e. t being a derived / temporary table ? – grego Mar 19 at 16:16
  • 1
    Yes. "t" is effectively a temporary, derived table. – pmdba Mar 19 at 16:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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