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

[Tim Horton SQL]

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';

[DA Morgan]

No version number? Why?

Don't use views ... use in-line views. And outer join to the in-line
view.

--
Daniel A. Morgan
http://www.psoug.org
damo...@x.washington.edu
(replace x with u to respond)