Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Outer joins in Oracle vs outer joins in Sybase

Outer joins in Oracle vs outer joins in Sybase

From: Amy Davidson <davidson_at_spectec.it>
Date: Fri, 26 Feb 1999 08:56:52 +0100
Message-ID: <7b5kau$6di$1@nslave1.tin.it>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US