Re: SQL has me confused.

From: Kim Berg Hansen <kibeha_at_gmail.com>
Date: Wed, 16 Oct 2013 13:28:59 +0200
Message-ID: <CA+S=qd0gVj3Rd9UT49w-Hs4xi+1Ay0T9pcUUn3Nzb0VUxO=H0Q_at_mail.gmail.com>

Hiya
For 10g there is Bug 5368296 which is fixed in version 11. The bug states among other things:

When you cut this right down, and abstract to emp, there are differing behaviours between ANSI joins and Oracle syntax joins. ANSI joins appear to make an assumption that if there is a three way join and two of the columns are qualified, the third is assumed.

I have had other small issues now and then with ANSI join syntax in 10g, but most of my issues have been fixed in 11.1 and the rest in 11.2, so now (on 11.2) I have not yet met trouble with ANSI style syntax :-)

Regards

Kim Berg Hansen

http://dspsd.blogspot.com
kibeha_at_gmail.com

_at_kibeha

On Wed, Oct 16, 2013 at 12:48 PM, Fred Tilly <ftilly_at_btinternet.com> wrote:

> 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
>
>
>

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

Received on Wed Oct 16 2013 - 13:28:59 CEST