ORA-01799: a column may not be outer-joined to a subquery
1011665May 29 2013 — edited May 29 2013Hi ,
I don't have much exposure on SQL queries.
We using Logical key concept in our Data model where in multiple keys represents a unique row in a table.
I have written a query using INNER JOIN and LEFT JOIN.I'm getting the below error when trying to execute it.the error is 'ORA-01799: a column may not be outer-joined to a subquery'.
Can someone please help me in correcting the SQL.
Below is the SQL.
SELECT CP.IP_IDFR, CP.CORRESPONDENCE_EVENT_ID,CDM.DELIVERY_METHOD,
CP.CP_WS_ID, CP.CP_LOCAL_TS, CP.CP_HOST_TS, CP.RULE_SYSTEM_ID, CP.LDBID
FROM CORRESPONDENCE_PREF CP INNER JOIN CORRESPONDENCE_EVENTDEL_METHOD CDM ON
CP.LDBID = 1 AND CP.IP_IDFR = 1 AND CP.IS_VISIBLE='Y' AND CP.CRUD_VALUE <> 'D' AND CDM.CRUD_VALUE <> 'D'
AND CP.DLVRY_METHOD_ID = CDM.DLVRY_METHOD_ID AND CP.CP_HOST_TS = (SELECT MAX(CP_HOST_TS) FROM TPTUX.CORRESPONDENCE_PREF CP1
WHERE CP1.LDBID = CP.LDBID AND CP1. IP_IDFR = CP.IP_IDFR and CP1.DLVRY_METHOD_ID=CP.DLVRY_METHOD_ID) AND
CDM.CEDM_HOST_TS=(SELECT MAX(CEDM_HOST_TS) FROM CORRESPONDENCE_EVENTDEL_METHOD CDM1 WHERE CDM1.DLVRY_METHOD_ID=CDM.DLVRY_METHOD_ID
AND CDM1.CORRESPONDENCE_EVENT_ID=CDM.CORRESPONDENCE_EVENT_ID AND CDM1.CRUD_VALUE <> 'D') LEFT JOIN CORRESPONDENCE_PREF_PARM CPP ON
CPP.IP_IDFR=CP.IP_IDFR
AND CP.CORRESPONDENCE_EVENT_ID = CPP.CORRESPONDENCE_EVENT_ID
AND CP.LDBID=CPP.LDBID
AND CPP.CRUD_VALUE <> 'D' AND CPP.CPP_HOST_TS = (SELECT MAX(CPP_HOST_TS) FROM CORRESPONDENCE_PREF_PARM CPP1 WHERE CPP.IP_IDFR = CPP1.IP_IDFR
AND CPP.CORRESPONDENCE_PARM_ID=CPP1.CORRESPONDENCE_PARM_ID AND CPP.LDBID=CPP1.LDBID AND CPP.CORRESPONDENCE_EVENT_ID=
CPP1.CORRESPONDENCE_EVENT_ID AND CPP1.CRUD_VALUE <> 'D')