Re: ORA-01799 outer join problem

On Apr 2, 3:36 pm, trp..._at_gmail.com wrote:
> When I run the following query, I get an ORA-01799 error complaining
> that a column may not be outer joined to a sub query. How can I re-
> write the following to get the desired result?
>
> SELECT A.CASE_ID,
> A.PROVIDER_GRP_ID,
> S.BO_NAME_DISPLAY,
> D.LONG_DESC,
> B.LONG_DESC,
> C.LONG_DESC,
> P.DESCR AS PRODUCT,
> TO_CHAR(A.ROW_ADDED_DTTM,'YYYY-MM-DD-HH24-MI-SS'),
> TO_CHAR(E.ROW_LASTMANT_DTTM,'YYYY-MM-DD-HH24-MI-SS'),
> A.RC_SUMMARY
>
> FROM
> ((((((PS_RC_CASE A LEFT OUTER JOIN PS_RC_PRIORITY_TBL B ON
> B.RC_PRIORITY = A.RC_PRIORITY )
> LEFT OUTER JOIN PS_RC_SEVERITY_TBL C ON C.RC_SEVERITY =
> A.RC_SEVERITY )
> LEFT OUTER JOIN PS_RC_STATUS_TBL D ON D.RC_STATUS = A.RC_STATUS AND
> D.SETID = 'JGLBL' )
> LEFT OUTER JOIN PS_BO_NAME S ON A.BO_ID_CUST = S.BO_ID AND
> S.PRIMARY_IND='Y' )
>
> LEFT OUTER JOIN PS_RC_ACTION_HIST E ON E.CASE_ID = A.CASE_ID AND
> E.EVENTNAME='AssignedToChanged'
> AND E.ROW_LASTMANT_DTTM = ( SELECT MAX(E.ROW_LASTMANT_DTTM) FROM
> PS_RC_ACTION_HIST E WHERE E.CASE_ID = A.CASE_ID AND
> E.EVENTNAME='AssignedToChanged'))
>
> LEFT OUTER JOIN PS_PROD_ITEM P ON P.PRODUCT_ID=A.PRODUCT_ID)
>
> WHERE A.PROVIDER_GRP_ID IN ('T2GSCC') AND A.RC_STATUS IN
> ('CUST','OPEN','OPEX','OPIN','RSRCH')
> AND A.ASSIGNED_TO = ' ' ORDER BY A.ROW_ADDED_DTTM DESC
I think I spot the problem but a copy of the actual Oracle error message along with the version information would have been nice.

Try moving the subquery "( SELECT MAX(E.ROW_LASTMANT ..." out of the JOIN condition and into the WHERE clause.

HTH -- Mark D Powell -- Received on Mon Apr 02 2007 - 17:35:08 CDT