RE: SQL has me confused.

  • From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 16 Oct 2013 11:06:47 +0000

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


Other related posts: