Re: Outer-Join in Oracle

"Christoph Graebener" <christoph.graebener_at_pronex.de> wrote in message news:7db947c0.0409301046.d47fd38_at_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

| >

| >

|
| 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

any error messages?

++ mcs Received on Thu Sep 30 2004 - 13:58:54 CDT