ORA-00904 使用別名時出現無效標識符錯誤

我試圖返回兩個子查詢的乘積，並認為我可以通過給每個子查詢加上別名來讓生活更輕鬆，然後將兩個別名相除以獲得我想要的數字，如下所示：

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

但這會導致 00904. 00000 - “%s: invalid identifier” 錯誤（Oracle 不喜歡Ha_IN_RKBor Ha_total）。

如下重寫查詢是可行的，但相當龐大：

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

有人知道為什麼我不能做這個ROUND( (Ha_IN_RKB / Ha_total) ,3)工作嗎？

別名不能直接使用。您必須使用嵌套子查詢或派生表將它們抽像出來。

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;

引用自：https://dba.stackexchange.com/questions/262241