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

Tim Horton SQL wrote:
> 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';
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
damorgan_at_x.washington.edu
(replace x with u to respond)