RE: should one use ANSI join syntax when writing an Oracle application?

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Wed, 18 Oct 2006 12:55:12 -0700
Message-ID: <1161201312.453686a0513c7@webmail.hosting.telus.net>

Quoting Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>: I need some help here. How do I code a full transitive closure join with the new syntax? In the traditional syntax I can say:

select A.x, B.y, C.z
from A, B, C

where A.x = B.x
  and B.x = C.x
  and A.x = C.x

/

How do I do that with the newfangled syntax. When I try

select A.x, B.y, C.z
from A

 inner join B on ( A.x = B.x )
 inner join C on ( B.x = C.x )
 inner join C on ( A.x = C.x )

;

I get
select A.x, B.y, C.z
*
ERROR at line 1:
ORA-00918: column ambiguously defined

> And to answer this question: converting from the traditional syntax to newer
> ANSI syntax is easy, use the inner join and on keywords.
>
> select ... from A, B where A.x < B.y
> becomes
> select ... from A inner join B on ( A.x < B.y )
>
> select ... from A, B where A.x between B.y and B.z
> becomes
> select ... from A inner join B on ( A.x between B.y and B.z )
>

-- 
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 18 2006 - 14:55:12 CDT