Re: ORA-00904 & left join

VC wrote:

> Daniel,
>
> Both the 'OUTER' and 'INNER' keywords are optional in SQL '92.
>
> Rgds.
>
> "Daniel Morgan" <damorgan_at_x.washington.edu
> <mailto: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
>>
>>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)
>

Thanks. I had never seen it the other way.

Well there's always time to update the curriculum for one more variation. ;-)

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