Re: ORA-00904 & left join

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

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)