Oracle嵌套SELECT查询报错ORA-00904：如何解决？

错误原因 #

原SQL报错核心是多层嵌套子查询的作用域限制：Oracle中，最内层的排序子查询无法直接引用最外层表A的CUST列——子查询的作用域仅支持逐层向上访问，跨多层的列引用会被判定为无效标识符。

解决方案 #

方案1：使用窗口函数ROW_NUMBER()（推荐，兼容性好且高效） #

SELECT 
    A.FIELD1,
    B.PCN
FROM TABLE1 A
LEFT JOIN (
    SELECT 
        CUST,
        PCN,
        -- 按CUST分组，每组内按PRIORITY排序生成序号
        ROW_NUMBER() OVER (PARTITION BY CUST ORDER BY PRIORITY) AS rn
    FROM TABLE2
) B ON A.CUST = B.CUST AND B.rn = 1;

说明：先给TABLE2的每条记录按CUST分组、PRIORITY排序标记序号，再与TABLE1关联取每组第一条（优先级最高）的PCN，LEFT JOIN确保TABLE1无对应记录时PCN显示NULL。

方案2：简化子查询层级（Oracle 12c+适用） #

SELECT 
    A.FIELD1,
    (SELECT PCN 
     FROM TABLE2 B 
     WHERE B.CUST = A.CUST 
     ORDER BY B.PRIORITY 
     -- 直接取排序后的第一条记录
     FETCH FIRST 1 ROW ONLY) AS PCN
FROM TABLE1 A;

说明：去掉多余的嵌套层级，直接在关联子查询中排序后取第一条，此时子查询能直接访问外层表A的列，规避作用域问题。

方案3：使用Oracle特有的KEEP子句（兼容低版本Oracle） #

SELECT 
    A.FIELD1,
    -- 按CUST分组后，取PRIORITY最高的PCN
    MAX(B.PCN) KEEP (DENSE_RANK FIRST ORDER BY B.PRIORITY) AS PCN
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.CUST = B.CUST
GROUP BY A.FIELD1, A.CUST;

说明：通过KEEP子句指定在分组内优先按PRIORITY排序，取第一条的PCN，MAX()仅作为聚合函数载体，不影响最终结果。

内容的提问来源于stack exchange，提问作者Maverick.pe