Re: Outer-Join in Oracle

From: Antonio Sant <asant_at_inwind_no_spam.it>
Date: Wed, 29 Sep 2004 20:03:51 GMT
Message-ID: <HyE6d.14881$H11.448885@twister1.libero.it>

Well,
Supposing that vbet outer ladvb stand for "ladvb is the table where in outer-join":

I think that something like:

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,ladvb
 WHERE    vbet.vfschl = :gvnSchlüsselVerf
          AND ladvb.vfschl = :gvnSchlüsselVerf(+) //not sure, try   AND
ladvb.vfschl (+)= :gvnSchlüsselVerf   if problem arise
          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

This sintax is pre Oracle9 (maybe pre Oracle 8i), on a "younger" Oracle u can do:

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 left /*or you can use rigth or full */ outer join ladvb on

(ladvb.vbschl = vbet.schl)
 WHERE vbet.vfschl = :gvnSchlüsselVerf

          AND ladvb.vfschl = :gvnSchlüsselVerf
          AND ladvb.termschl = :gvnSchlüsselEinzeltermine
//REMOVED          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

Bye!
Antonio

P.S.
If it work, let me know... ;-)

"Christoph Graebener" <christoph.graebener_at_pronex.de> ha scritto nel messaggio 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
Received on Wed Sep 29 2004 - 15:03:51 CDT