主页 / dba / 问题 / 262241
Accepted
grego
Asked: 2020-03-19 16:48:40 +0800 CST

ORA-00904 使用别名时出现无效标识符错误

  • 772

我试图返回两个子查询的乘积，并认为我可以通过给每个子查询加上别名来让生活更轻松，然后将两个别名相除以获得我想要的数字，如下所示：

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)工作吗？

oracle-12c alias

1 个回答

  1. Best Answer

    别名不能直接使用。您必须使用嵌套子查询或派生表将它们抽象出来。

    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;
    • 1

相关问题