| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
Home -> Community -> Usenet -> c.d.o.server -> Outer joins in Oracle vs outer joins in Sybase
Dear Oracle and Sybase developers or dbas,
Did I convert the following Sybase outer join to the correct Oracle outer join? The query passes the syntax checker in Powerbuilder but I'm not sure if I've created the same query.
Sybase outer join:
SELECT "sparepart"."partno" ,
"sparepart"."name" ,
"sparelocation"."locationid" ,
"sparepart"."stockunitid" ,
"sparepart"."avgprice" ,
"sparepart"."avgpricecurr" ,
"sparelocation"."instock" ,
"sparepart"."deptid" ,
"sparepart"."avgprice" * "sparelocation"."instock" stock_value,
"sparepart"."stockeditem" ,
"component"."compname" ,
"sparepartlog"."lastupdated" ,
"component"."compno"
FROM {oj {oj {oj {oj {oj "sparepart" LEFT OUTER JOIN "address" address_a ON "sparepart"."primaryvendorid" = "address_a"."addressid"} LEFT OUTER JOIN "address" address_b ON "sparepart"."makerid" = "address_b"."addressid"} LEFT OUTER JOIN "sparelocation" ON "sparepart"."partid" = "sparelocation"."partid"} LEFT OUTER JOIN "compspare" ON "sparepart"."partid" = "compspare"."parttypeid"} LEFT OUTER JOIN "component" ON "compspare"."comptypeid" = "component"."compid"}, {oj "sparepart" LEFT OUTER JOIN "sparepartlog" ON "sparepart"."partid" = "sparepartlog"."partid"} , "address" "address_a" , "address" "address_b" WHERE ( "sparepartlog"."partid" is null )
Oracle outer join:
SELECT sparepart.partno,
sparepart.name,
sparelocation.locationid,
sparepart.stockunitid,
sparepart.avgprice,
sparepart.avgpricecurr,
sparelocation.instock,
sparepart.deptid,
(sparepart.avgprice * sparelocation.instock) stock_value,
sparepart.stockeditem,
component.compname,
sparepartlog.lastupdated,
component.compno
FROM sparepart,
address address_a,
address address_b,
sparelocation,
compspare,
component,
sparepartlog
WHERE ( sparepart.primaryvendorid = address_a.addressid (+)) and
( sparepart.makerid = address_b.addressid (+)) and
( sparepart.partid = sparelocation.partid (+)) and
( sparepart.partid = compspare.parttypeid (+)) and
( compspare.comptypeid = component.compid (+)) and
( sparepart.partid = sparepartlog.partid (+)) and
( sparepartlog.partid = null )
Received on Fri Feb 26 1999 - 01:56:52 CST
![]() |
![]() |