Re: Outer-Join in Oracle

From: Christoph Graebener <christoph.graebener_at_pronex.de>
Date: 30 Sep 2004 11:46:56 -0700
Message-ID: <7db947c0.0409301046.d47fd38@posting.google.com>

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:<odadnb4z7s4ybMbcRVn-gw_at_comcast.com>...
> "Christoph Graebener" <christoph.graebener_at_pronex.de> wrote in message
> news:7db947c0.0409291055.1d7ca7ac_at_posting.google.com...
> | Hallo together,
> |
> | I have to work out an sql-statement which was former runnning on an
> | Informix-Database. This has to be converted to the Oracle-Outer-Join
> | Syntax.
> |
> | My problem is that I have tried many different possibilites of
> | formulation the select-Statement, but without effort.
> |
> | Does anyone has an idea how to formulate the following
> | Select-Statement, which is according to the syntax-rules of an
> | informix-database, into a correct working Oracle-Statement?
> |
> | SELECT vbet.kname, vbet.stell, ladvb.alad, ladvb.vbschl, vbet.kenn,
> | vbet.lskenn,
> | vbet.zuord, vbet.schl, vbet.sort, ladvb.schl, ladvb.termschl,
> | ladvb.dlad,
> | ladvb.zlad, ladvb.alad, ladvb.bstlad, vbet.kuerz
> | FROM vbet, outer ladvb
> | WHERE vbet.vfschl = :gvnSchlüsselVerf
> | AND ladvb.vfschl = :gvnSchlüsselVerf
> | AND ladvb.termschl = :gvnSchlüsselEinzeltermine
> | AND ladvb.vbschl = vbet.schl
> | AND ladvb.dlad = (SELECT MAX (dlad)
> | FROM ladvb
> | WHERE ladvb.vbschl = vbet.schl
> | AND ladvb.termschl =
> | :gvnSchlüsselEinzeltermine)
> | AND (ladvb.zlad = (SELECT MAX (zlad) FROM
> | ladvb
> | WHERE ladvb.vbschl = vbet.schl
> | AND ladvb.termschl =
> | :gvnSchlüsselEinzeltermine
> | AND (ladvb.dlad = (SELECT MAX (dlad)
> | FROM ladvb
> | WHERE ladvb.vbschl =
> |
> | vbet.schl
> | AND ladvb.termschl =
> |
> | :gvnSchlüsselEinzeltermine))))
> | AND ladvb.schl = (SELECT MAX
> | (ladvb.schl) from ladvb
> | WHERE ladvb.vbschl =
> | vbet.schl AND
> | ladvb.termschl =
> | :gvnSchlüsselEinzeltermine)
> | ORDER BY sort
> |
> | Thanks for any help in advance.
> |
> | Christoph
>
> what version of oracle?
> what have you tried?
> what results (errors) did you get?
>
> ++ mcs

the version is Oracle 9i R2
the results were not that I became from the Informix-Statement. That was, what I tried:

Version I:  

SELECT vbet.kname, vbet.stell, ladvb.alad, ladvb.vbschl, vbet.kenn, vbet.lskenn,

         vbet.zuord, vbet.schl, vbet.sort, ladvb.schl, ladvb.termschl, ladvb.dlad,

         ladvb.zlad, ladvb.alad, ladvb.bstlad, vbet.kuerz 
FROM     vbet right join ladvb 
ON       ladvb.vbschl = vbet.schl
WHERE    vbet.vfschl = :gvnSchlüsselVerf
         AND ladvb.vfschl = :gvnSchlüsselVerf
         AND ladvb.termschl = :gvnSchlüsselEinzeltermine
         AND ladvb.dlad = (SELECT MAX (dlad) 
                           FROM ladvb
                           WHERE ladvb.vbschl = vbet.schl
                           AND ladvb.termschl =
:gvnSchlüsselEinzeltermine)
                           AND (ladvb.zlad = (SELECT MAX (zlad) FROM
ladvb
                             WHERE ladvb.vbschl = vbet.schl
                               AND ladvb.termschl =
:gvnSchlüsselEinzeltermine
                               AND (ladvb.dlad = (SELECT MAX (dlad) 
                                 FROM ladvb 
                                  WHERE ladvb.vbschl = vbet.schl
                                    AND ladvb.termschl =
:gvnSchlüsselEinzeltermine))))
                                    AND ladvb.schl = (SELECT MAX
(ladvb.schl) from ladvb
                                           WHERE ladvb.vbschl =
vbet.schl AND
                                                 ladvb.termschl =

:gvnSchlüsselEinzeltermine)
ORDER BY sort

/

Version II  

SELECT v.kname, v.stell, l.alad, l.vbschl, v.kenn, v.lskenn, v.zuord, v.schl,

          v.sort, l.schl, l.termschl, To_Date(l.dlad, 'DD.MM.YYYY'), 
          To_Char(l.zlad, 'HH24:MI'), l.alad, l.bstlad, v.kuerz  
  FROM    vbet v, ladvb l   
WHERE     v.schl = l.vbschl(+)  AND 
          v.vfschl = l.vfschl(+)  AND 
          200281 = l.termschl(+)  AND          
          v.vfschl = :gvnSchlüsselVerf  AND
          v.tabtyp = 'V' AND 
          l.dlad = 
             (SELECT MAX (dlad) 
              FROM   ladvb l1
              WHERE  v.schl = l1.vbschl(+) AND
                     :gvnSchlüsselEinzeltermine = l1.termschl(+) ) AND
          (l.zlad = 
             (SELECT MAX (zlad) 
              FROM   ladvb l2
              WHERE  v.schl = l2.vbschl(+) AND
                     :gvnSchlüsselEinzeltermine = l2.termschl(+) ) AND
                     (l.dlad = 
                         (SELECT MAX (dlad) 
                          FROM   ladvb l3
                          WHERE  v.schl = l3.vbschl(+) AND
                                 :gvnSchlüsselEinzeltermine =
l3.termschl(+)))) AND
          l.schl = 
             (SELECT MAX (schl) 
              FROM   ladvb l4
              WHERE  v.schl = l4.vbschl(+) AND
                     :gvnSchlüsselEinzeltermine = l4.termschl(+))

ORDER BY sort

Regards
Christoph Received on Thu Sep 30 2004 - 13:46:56 CDT