Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

1011665May 29 2013 — edited May 29 2013
Hi ,
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')
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 26 2013
Added on May 29 2013
3 comments
14,903 views