Step 1: Look at the execution plan and check the actual content of the predicate section. It may show you transitive closure generating extra copies of the constant predicate, it may should you alias of columns matched against the predicate. Like you, however, I would have expected "ORA-00918: column ambiguously defined". Which version of 10g ? Regards Jonathan Lewis ________________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf of Fred Tilly [ftilly@xxxxxxxxxxxxxx] Sent: 16 October 2013 11:48 To: oracle-l@xxxxxxxxxxxxx Subject: SQL has me confused. Hi, I thought I understood sql until I came across this which has me confused. This is on an Oracle 10g database. I have removed some columns from the select to just show my problem. If I run this sql it runs and generates the output shown below it. SELECT CCV.CASEID, CHDR.caseid, cfi.caseid FROM lgncc_commoncaseview ccv INNER JOIN LGNCC_CASEHDR CHDR ON CCV.CASEID = CHDR.CASEID inner join flods_classification_d00 class ON (class.flods_id = chdr.titleid) INNER JOIN LGNCC_CASEQUEUE CQ ON CHDR.ALLOCATEDTODEPTID = CQ.QUEUEID LEFT OUTER JOIN LGNOM_PARTYADDRESS PA ON (CCV.XREF1 = PA.PARTYID AND PA.PREFERRED=1) LEFT OUTER JOIN LGNCC_CASEFORMINSTANCE CFI ON CFI.CASEID = CCV.CASEID WHERE CASEID IN (10101059,1061619) CASEID CASEID CASEID ---------- ---------- ---------- 10101059 10101059 10101059 However what I would have expected was that oracle would tell me that the column caseid in the where clause was ambiguously defined since caseid occurs in multiple tables/views in the query. If I change the sql to: SELECT CCV.CASEID, CHDR.caseid, cfi.caseid FROM lgncc_commoncaseview ccv INNER JOIN LGNCC_CASEHDR CHDR ON CCV.CASEID = CHDR.CASEID INNER JOIN FLODS_CLASSIFICATION_D00 CLASS ON (CLASS.FLODS_ID = CHDR.TITLEID) INNER JOIN LGNCC_CASEQUEUE CQ ON CHDR.ALLOCATEDTODEPTID = CQ.QUEUEID LEFT OUTER JOIN LGNOM_PARTYADDRESS PA ON (CCV.XREF1 = PA.PARTYID AND PA.PREFERRED=1) LEFT OUTER JOIN LGNCC_CASEFORMINSTANCE CFI ON CFI.CASEID = CCV.CASEID WHERE ccv.CASEID IN (10101059,1061619) CASEID CASEID CASEID ---------- ---------- ---------- 10101059 10101059 10101059 1061619 1061619 I get a different result. From the result it would appear that oracle in the first statement is applying the where clause to the LGNCC_CASEFORMINSTANCE table, would this be what we expect, our would we expect the error that the column is ambiguously defined ? Thanks Fred -- //www.freelists.org/webpage/oracle-l-- //www.freelists.org/webpage/oracle-l