Re: ORA-01799 outer join problem

From: <trpost_at_gmail.com>
Date: 3 Apr 2007 09:42:05 -0700
Message-ID: <1175618525.460374.294310@y80g2000hsf.googlegroups.com>

Ok, so I moved the subquery out of the join condition and into the where clause:

SELECT A.CASE_ID AS "Case ID", A.PROVIDER_GRP_ID AS "Assigned To", S.BO_NAME_DISPLAY AS "Customer", D.LONG_DESC AS "Status", B.LONG_DESC AS "Priority", C.LONG_DESC AS "Severity", P.DESCR AS "Product", TO_CHAR(A.ROW_ADDED_DTTM,'YYYY-MM-DD-HH24-MI-SS') AS "Date Created
(Age)",

TO_CHAR(E.ROW_LASTMANT_DTTM,'YYYY-MM-DD-HH24-MI-SS') AS "Time in queue", A.RC_SUMMARY AS "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')

         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 = ' '
	  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')

ORDER BY A.ROW_ADDED_DTTM DESC The above now returns results, however by moving the subquery to the WHERE clause I am not getting all the results that I would expect. If it were part of the LEFT OUTER JOIN, I would get values returned that were null, but by putting the subquery in the WHERE, I will not get rows where SELECT MAX(...) is null. That why it made sense to me to have it as part of the join.

I basically want to find the MAX(E.ROW_LASTMANT_DTTM) or null if there is no value. Received on Tue Apr 03 2007 - 11:42:05 CDT