SQL has me confused.

From: Fred Tilly <ftilly_at_btinternet.com>
Date: Wed, 16 Oct 2013 11:48:19 +0100 (BST)
Message-ID: <1381920499.16566.YahooMailNeo_at_web87404.mail.ir2.yahoo.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 16 2013 - 12:48:19 CEST