Re: SQL has me confused.
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: kibeha@xxxxxxxxx
- Date: Wed, 16 Oct 2013 14:54:57 +0200
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@xxxxxxxxx>
> 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@xxxxxxxxxxxxxxxxxx
> > wrote:
>
> >
> > Is that a challenge ?
> >
> > ________________________________________
> > From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
> > behalf of Kim Berg Hansen [kibeha@xxxxxxxxx]
> > Sent: 16 October 2013 12:28
> > To: ftilly@xxxxxxxxxxxxxx
> > Cc: oracle-l@xxxxxxxxxxxxx
> > Subject: Re: SQL has me confused.
> >
> > so now (on 11.2) I have not yet met trouble with ANSI stylesyntax :-)
> >
> >
> > Regards
> >
> >
> > Kim Berg Hansen
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Bien Respectueusement
Mohamed Houri
--
//www.freelists.org/webpage/oracle-l
Other related posts:
