Re: SQL has me confused.
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-lReceived on Wed Oct 16 2013 - 14:54:57 CEST