ORA-01799:列可能未外部联接到子查询 [英] ORA-01799: a column may not be outer-joined to a subquery

问题描述

这是我的查询

SELECT 
    COUNT(C.SETID)
FROM 
    MYCUSTOMER C
    LEFT OUTER JOIN MYCUSTOPTION CO 
    ON 
        (C.SETID = CO.SETID 
            AND C.CUST_ID = CO.CUST_ID 
            AND CO.effdt = ( 
                SELECT MAX(COI.EFFDT) 
                FROM MYCUSTOPTION COI 
                WHERE 
                    COI.SETID = CO.SETID 
                                    AND COI.CUST_ID = CO.CUST_ID 
                                    AND COI.EFFDT <=SYSDATE    
                )
    )

这是我收到的错误消息.

and here is the error message that I am getting..

我在做什么错??

推荐答案

您可以通过推送子查询以使其不外部联接来重写它:

you can rewrite that by pushing the sub query so that its not outer joined:

select Count(C.setid)
  from mycustomer C
       left outer join (select *
                          from mycustoption co
                         where co.effdt <= (select Max(COI.effdt)
                                              from mycustoption COI
                                             where COI.setid = co.setid
                                               and COI.cust_id = co.cust_id
                                               and COI.effdt <= sysdate)) co
                    on ( C.setid = CO.setid
                         and C.cust_id = CO.cust_id ) 

这篇关于ORA-01799:列可能未外部联接到子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！