Oracle创建视图遇ORA-01799错误：列无法与子查询外连接的解决咨询

错误原因 #

你遇到的ORA-01799错误并非来自你怀疑的第一个子查询，而是左连接（LEFT JOIN）的ON子句中直接引用了子查询。Oracle数据库不支持在外部连接的连接条件里嵌套子查询，具体触发错误的是dispute和adjustment表连接时的(SELECT Trunc(SYSDATE, 'mm') FROM dual)部分。

另外，你的查询存在一处笔误：adjustment表的连接条件里错误引用了d.created_dtm（dispute表的字段），应该改为ad.created_dtm，否则会导致逻辑偏差。

修复方案（不改变原有查询逻辑） #

1. 移除ON子句中的子查询：Trunc(SYSDATE, 'mm')是标量表达式，可直接使用，无需嵌套SELECT ... FROM dual
2. 修正adjustment表连接条件的字段引用错误

修正后的完整SQL #

SELECT DISTINCT a.account_num
FROM   (SELECT account_num,
               Max(effective_dtm) EFFECTIVE_DTM
        FROM   accountstatus
        GROUP  BY account_num) a
       JOIN accountstatus b
         ON b.account_num = a.account_num
            AND b.effective_dtm = a.effective_dtm
            AND b.account_status = 'OK'
       LEFT JOIN (SELECT account_num,
                         Max(bill_seq) bill_seq
                  FROM   ert_invoice
                  GROUP  BY account_num) last_bills
              ON last_bills.account_num = a.account_num
       JOIN ert_invoice inv
         ON inv.account_num = last_bills.account_num
            AND inv.bill_seq = last_bills.bill_seq
            AND inv.suppress_status = 3
            AND inv.bill_type_id = 1
       LEFT JOIN dispute d
              ON d.account_num = a.account_num
                 AND d.created_dtm >= Trunc(SYSDATE, 'mm')
                 AND d.dispute_status = 'P'
       LEFT JOIN adjustment ad
              ON ad.account_num = a.account_num
                 AND ad.created_dtm >= Trunc(SYSDATE, 'mm')
                 AND ad.adjustment_status IN ( '1', '3' )
       LEFT JOIN accountattributes aa
              ON aa.account_num = a.account_num
                 AND aa.account_sign IN ( 'PRI', 'TST' )
WHERE  d.account_num IS NULL
       AND ad.account_num IS NULL
       AND aa.account_num IS NULL;

可选优化建议 #

你可以用窗口函数替换第一个子查询的分组逻辑，让查询更简洁，同时完全保留原有逻辑：

SELECT DISTINCT a.account_num
FROM   (SELECT account_num,
               effective_dtm,
               ROW_NUMBER() OVER(PARTITION BY account_num ORDER BY effective_dtm DESC) rn
        FROM   accountstatus) a
       JOIN accountstatus b
         ON b.account_num = a.account_num
            AND b.effective_dtm = a.effective_dtm
            AND b.account_status = 'OK'
            AND a.rn = 1
       -- 后续连接和WHERE条件与修正后的SQL一致

内容的提问来源于stack exchange，提问作者Belova NI