Re: ORA-01799 outer join problem

On Apr 3, 5:21 am, "William Robertson" <williamr2..._at_googlemail.com> wrote:
> On Apr 2, 8: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
>
> btw it would be a bit easier to read without all those redundant
> brackets.- Hide quoted text -
>
> - Show quoted text -

I agree with William on the query formatting. If you need to filter the one table by the subquery prior to performing the outer join operation to get the results you want then I think you should look to see if you can turn the table sub-query into an inline view so the the sub-query filtering happens prior to the join operation of this result set to the rest of the join.

When you really need help with a query posting a stripped down create table(s) with inserts for sample data encourgages people with limited time but an interest in helping to take a couple of minutes and attemp to manipulate the query.

HTH -- Mark D Powell -- Received on Fri Apr 06 2007 - 08:40:47 CDT