Re: ORA-00904 & left join

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1066751182.356553_at_yasure...
> Fabio wrote:
>
> >Oracle 9i:
> >Situation:
> >
> > create table v(x number, y varchar2(10));
> > create table b(z number, w varchar2(10));
> > create table t(h number, k varchar2(10));
> >
> >-- OK
> >1)
> > select v.*
> > from
> > v left join (b inner join t on b.Z = t.H) on v.X = t.H
> >
> >-- error ORA-00904
> >2)
> > select v.x, v.y
> > from
> > v left join (b inner join t on b.Z = t.H) on v.X = t.H
> >
> >-- OK
> >
> >What's wrong in the query 1 ??
> >
> >Thanks
> >Fabio
> >
> >
> >
> The syntax? Where did you get the idea that you could write this in
> Oracle? Oracle is Oracle ...
> it is not a Microsoft product.
>
> Go to http://tahiti.oracle.com and look up the correct syntax.
>

Hello Daniel,

I am sorry to say but you are wrong:

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Oct 21 12:45:28 2003

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production

SQL> create table v(x number, y varchar2(10));
    create table b(z number, w varchar2(10));
    create table t(h number, k varchar2(10));

Table created.

SQL>
Table created.

SQL>
Table created.

SQL> select v.*
 from
 v left join (b inner join t on b.Z = t.H) on v.X = t.H   2 3 4
SQL> / no rows selected

SQL>

Fabio used the standard ANSI '92 syntax that Oracle presumably implements in 9i, and MS SQL Server has got nothing to do with Fabio's problem.

The correct answer would be to upgrade to a less buggy version of Oracle where this _bug_ was fixed. Apparently Fabio is running something below 9.2.0.4.

Rgds. Received on Tue Oct 21 2003 - 11:56:04 CDT