Re: SQL has me confused.

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Wed, 16 Oct 2013 14:54:57 +0200
Message-ID: <CAJu8R6irgLYLudnGjuwcqmE47gqovQKOinTjNwhG2HO2f5ocOw_at_mail.gmail.com>



The following query (reduced to the bare maximum) was running perfectly in 10.2.0.4
WITH det AS

        (SELECT           p.col_pk,

          p.dat_col

        FROM              Table_a p

        LEFT JOIN              Table_b j

          ON j.col_pk = p.col_pk

          AND j.dat_col = p.dat_col

        LEFT JOIN              Table_c s

        ON s.col_pk = p.col_pk

        AND s.dat_col= p.dat_col

        WHERE p.dat_col = pid_dat_col

        AND p.col_pk = pin_col_pk

        AND (col_pk IS NOT NULL OR s.col_pk IS NOT NULL) ----> focus here

        and ((ln_in_num = 0

                and (j.appl_org = 'X27'

                 or (s.appl_org = 'X27' and j.col_pk is null)

                    )

              )

                 or ln_in_num = 1

            )

         )

SELECT           p.col_pk,

          p.dat_col,

          p.ord_no

 FROM det

 JOIN Table_a p

 ON det.col_pk=p.col_pk

 AND det.dat_col=p.dat_col

 ;

 Until we have upgraded to 11.0.2.3 where it started crashing with ORA-00918: column ambiguously defined

The solution has been to change the query by adding the appropriate alias as shown below:

WITH det AS

        (SELECT           p.col_pk,

          p.dat_col

        FROM              Table_a p

        LEFT JOIN              Table_b j

          ON j.col_pk = p.col_pk

          AND j.dat_col = p.dat_col

        LEFT JOIN              Table_c s

        ON s.col_pk = p.col_pk

        AND s.dat_col= p.dat_col

        WHERE p.dat_col = pid_dat_col

        AND p.col_pk = pin_col_pk

        AND (*j*.col_pk IS NOT NULL OR s.col_pk IS NOT NULL) ----> focus here

        and ((ln_in_num = 0

                and (j.appl_org = 'X27'

                 or (s.appl_org = 'X27' and j.col_pk is null)

                    )

              )

                 or ln_in_num = 1

            )

         )

SELECT           p.col_pk,

          p.dat_col,

          p.ord_no

 FROM det

 JOIN Table_a p

 ON det.col_pk=p.col_pk

 AND det.dat_col=p.dat_col

 ;

 Best regards

Mohamed Houri

www.hourim.wordpress.com

2013/10/16 Kim Berg Hansen <kibeha_at_gmail.com>

> Nah, I did write "yet" - meaning that I personally have not bumped into
> ANSI problems on 11.2 yet - I did not state that it was all good and fixed
> ;-) I just meant ANSI syntax in 11.2 is at least a good deal better in 11.2
> than it was in version 10 (in my experience - I cannot speak for everyone
> ;-)

>
>
>
>

> On Wed, Oct 16, 2013 at 1:53 PM, Jonathan Lewis <
> jonathan_at_jlcomp.demon.co.uk
> > wrote:
>

> >
> > Is that a challenge ?
> >
> > ________________________________________
> > From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
> > behalf of Kim Berg Hansen [kibeha_at_gmail.com]
> > Sent: 16 October 2013 12:28
> > To: ftilly_at_btinternet.com
> > Cc: oracle-l_at_freelists.org
> > Subject: Re: SQL has me confused.
> >
> > so now (on 11.2) I have not yet met trouble with ANSI stylesyntax :-)
> >
> >
> > Regards
> >
> >
> > Kim Berg Hansen
>
>

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


-- 
Bien Respectueusement
Mohamed Houri


--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 16 2013 - 14:54:57 CEST

Original text of this message