Re: ORA-00918 expected but ...

On Apr 11, 10:29 am, "Torsten Edler" <Torsten.Ed..._at_googlemail.com> wrote:
> I have a small example (which i have stripped down from a real world
> problem) which puzzles me. It consists of an outer join of three
> tables where i would expect to get an ORA-00918 "column ambiguously
> defined" but Oracle 10.2.0.3.0 on Windows accepts it perfectly well.
>
> create table t1 (x number, y1 number);
> create table t2 (x number, y2 number);
> create table t3 (x number, y2 number);
>
> insert into t1 values (1,1);
> insert into t1 values (2,2);
> insert into t3 values (2,2);
>
> select distinct t1.x, y1, x, t2.x, a.x
> from t1
> left outer join t2 on t1.x=t2.x
> left outer join t3 a on t1.x=a.x
> ;
>
> the db chooses x automatically to mean t2.x instead of complaining
> about ambiguity whereas if t3 is left out of the join
>
> select distinct t1.x, y1, x, t2.x
> from t1
> left outer join t2 on t1.x=t2.x
> ;
>
> an ORA-00918 is received because x is ambiguous. Is this a bug or my
> lack of understanding?

This looks like a know issue that started on 9.0 I do not have the bug number. The problem only exists when the newer ANSI join syntax is used. The workaround is to label your columns so that you specify which table.column to return.

Sample code that should reproduce the problem but I do not have 10.2.0.3 to test with.

set echo on
-- posted to Oaktable 8/5/2004 as ANSI standard Joins and Ambiguous columns

drop table parent1 ;

create table parent1 ( key number(4) );

insert into parent1 values ( 1 );
insert into parent1 values ( 2 );
insert into parent1 values ( 3 );
insert into parent1 values ( 4 );

drop table child1 ;

create table child1 ( key number(4), ambiguous_column varchar(1) );

insert into child1 values ( 1, 'A' );
insert into child1 values ( 2, 'B' );
insert into child1 values ( 3, null );

drop table child2 ;

create table child2 ( ambiguous_column varchar(1), value number(4) );

insert into child2 values ( 'A', 1 );
insert into child2 values ( 'Z', 26 );

select * from parent1 ;
select * from child1 ;
select * from child2 ;

select key, ambiguous_column from parent1 left outer join child1 using (key)
 order by key ;

• This is the query of interest select key, ambiguous_column from parent1 P1 left outer join child1 C1 using (key) left outer join child2 C2 on ( C1.ambiguous_column = C2.ambiguous_column ) order by key ;

select key, C1.ambiguous_column from parent1 P1 left outer join child1 C1
 using (key) left outer join child2 C2
 on ( C1.ambiguous_column = C2.ambiguous_column ) order by key;

HTH -- Mark D Powell -- Received on Wed Apr 11 2007 - 10:21:25 CDT