Re: ORA-00904 & left join

From: VC <boston103_at_hotmail.com>
Date: Tue, 21 Oct 2003 19:28:53 GMT
Message-ID: <UNflb.198407$%h1.191815@sccrnsc02>

Daniel,

Both the 'OUTER' and 'INNER' keywords are optional in SQL '92.

Rgds.
"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1066759341.634982_at_yasure... VC wrote:

"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

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> 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.

Perhaps my mistake. I know, and even occassionally use the ANSI syntax. But it seemed to me that his usage was not the way it is in Oracle. Perhaps I replied to quickly but the syntax I teach and use is:

SELECT p.last_name, t.title_name
FROM person p LEFT OUTER JOIN person_title t ON p.title_1 = t.title_abbrev;

Whereas what I read above was:

"LEFT JOIN" ... missing the word "OUTER"

How about it Jonathan? Did I blow it?

--

Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu

(replace 'x' with a 'u' to reply) Received on Tue Oct 21 2003 - 14:28:53 CDT