Outer-Join in Oracle

From: Christoph Graebener <christoph.graebener_at_pronex.de>
Date: 29 Sep 2004 11:55:21 -0700
Message-ID: <7db947c0.0409291055.1d7ca7ac@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 - 13:55:21 CDT