Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> ORA-01799: a column may not be outer-joined to a subquery
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