oracle - ORA-01799:列可能未外部联接到子查询

这是我的查询

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
                )
    )

这是我收到的错误消息。

我究竟做错了什么？？？

最佳答案

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

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 )