ORA-01799: a column may not be outer-joined to a subquery

Question

Here is my query

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

What am I doing wrong???

Answer 1

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 )  

Answer 2

Well, Oracle apparently doesn't support using a subquery inside the join condition for an outer join. So you need to get rid of the subquery.

The question is, why is it there at all? You have "<=" conditions in two places, so the predicate essentially says "all records whose effective date is no later than the latest effective date that is no later than now". If that's what you really want, you could simplify it to "all records whose effective date is no later than now", i.e.:

ON      (C.SETID = CO.SETID          AND C.CUST_ID = CO.CUST_ID          AND CO.effdt <= SYSDATE     ) 

Voila, no subquery.

But is that really what you want, or did you mean that first "<=" to be just "=" -- i.e. find the record with the most recent effective date before now? If that's what you really want, it will be more complex to rewrite.