Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> ORA-01799: a column may not be outer-joined to a subquery

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

From: Tim Horton SQL <oscar_ye_at_hotmail.com>
Date: 7 Jun 2005 06:20:29 -0700
Message-ID: <1118150428.997036.254060@f14g2000cwb.googlegroups.com>


Hi SQL gurus!

I am getting the following error when I run my sql:

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

The solution provided is to use views or remove the outer join. Since I cannot remove the outer join without affecting the results I am attempting to create views but i am having difficulties.

Any help on how to remove the subqueries would be really really apreciated! Here is the SQL:

 SELECT DISTINCT B.CUST_ID
, B.CUST_STATUS
, C.PAYMENT_METHOD
, C.PYMNT_TERMS_CD
, D.CREDIT_CLASS

  FROM PS_CUSTOMER B LEFT OUTER JOIN PS_CUST_DATA A ON (A.CUST_ID = B.CUST_ID)
  LEFT OUTER JOIN PS_CUST_OPTION C ON (B.CUST_ID = C.CUST_ID    AND B.SETID = C.SETID
   AND C.EFFDT = (
 SELECT MAX(C_ED.EFFDT)
  FROM PS_CUST_OPTION C_ED
 WHERE C.SETID = C_ED.SETID
   AND C.CUST_ID = C_ED.CUST_ID
   AND C_ED.EFFDT <= '01/01/1990 00:00:00')) LEFT OUTER JOIN PS_CUST_CREDIT D ON (B.CUST_ID = D.CUST_ID    AND D.SETID = B.SETID
   AND D.EFFDT = (
 SELECT MAX(D_ED.EFFDT)
  FROM PS_CUST_CREDIT D_ED
 WHERE D.SETID = D_ED.SETID
   AND D.CUST_ID = D_ED.CUST_ID
   AND D_ED.EFFDT <= '01/01/1990 00:00:00'))  WHERE B.SETID = 'TEST'; Received on Tue Jun 07 2005 - 08:20:29 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US