Re: ANSI Outer Joins

From: Matthias Gresz <GreMa_at_t-online.de>
Date: 13 Nov 1998 13:07:48 GMT
Message-ID: <72hav4$hsh$1@news02.btx.dtag.de>

Hi,

Simon Caldwell schrieb:
>
> Paul Moore wrote in message <364af9de.21722496_at_news.origin-it.com>...
> >On Thu, 12 Nov 1998 11:31:41 +0100, "Simon Caldwell"
> ><simonATgetrealsystemsDOTcom> wrote:
> >
> >>We have an application which runs on a number of other databases, and we
> now
> >>need to port to Oracle.
> >>
> >>The main problem we have, is that our SQL uses the ANSI standard SQL-92
> >>outer join syntax, which allows multi-table outer joins.
> >>I have looked on dejanews.com and can't find an answer to the problem,
> just
> >>discussion about levels of compliance with SQL-92.
> >>We need to be able to achieve the same affect as
> >>
> >>select a.col1, b.col2, c.col3
> >>from a, b, c
> >>where a.col1(+) = b.col1
> >> and a.col2(+) = c.col1
> >> and b.col4=c.col4
> >>
> >>which would be possible using SQL-92 syntax, which is supported by the
> other
> >>databases, but appears not to be using Oracle.
> >>
> >I don't understand your question. The syntax you quote works on
> >Oracle. If the syntax you are using is different, please post it...
> >
> >(Sorry, I don't know what SQL-92 looks like, so I can't say much abot
> >that without examples...)
> >
> >Paul Moore
> >
> No it doesn't. Note that A is outer joined to both B and C. This will give
> an error
> ORA-01416: two tables cannot be outer-joined to each other
>
> The SQL-92 syntax would be
> select a.col1, b.col2, c.col3
> from b
> JOIN c ON (b.col4=c.col4)
> LEFT OUTER JOIN a ON (a.col1 = b.col1 AND a.col2 = c.col1)

Maybe you should specify the version you use. With our NT 7.3.4.2.0 this works a few dozens times a day:

SELECT

a.NAME1,
A.Vorname,
A.Name2,
A.Strasse,
A.Ort,
A.Postfach,
A.Plz,
o.Anzobj,
m.anzMiet,
k.anzki,
S.anzsonst,
A.AdressNummer,
KB.ID
FROM     

V_Adressen A,
KUNDENBERATER KB,
(SELECT EIGENTUEMER ADRESSNUMMER,

	Count(OBJEKTEIGENTUEMER.EIGENTUEMERNUMMER) AS Anzobj 
FROM 	OBJEKTEIGENTUEMER GROUP BY  EIGENTUEMER ) o, 
(SELECT ADRESSNUMMER,
	Count(MIETER.GESUCHNUMMER) AS anzMiet 

FROM MIETER GROUP BY Adressnummer ) M, (SELECT ADRESSNUMMER,
        Count(KAUFINTERESSENTEN.AUFTRAGSNUMMER) AS anzki FROM KAUFINTERESSENTEN GROUP BY Adressnummer ) K, (SELECT ADRESSNUMMER,
        Count(SONSTIGE.SONSTIGENUMMER) AS anzsonst FROM SONSTIGE GROUP BY Adressnummer ) S WHERE
        ((A.Adressnummer = o.adressnummer (+) ) AND
        (A.Adressnummer = k.adressnummer (+) ) AND
        (A.Adressnummer = m.adressnummer (+) ) AND
        (A.Adressnummer = s.adressnummer (+) ) ) AND
        A.KBNR=KB.KBNUMMER(+)
;

Regards

Matthias

--
Matthias.Gresz_at_Privat.Post.DE

Always log on the bright side of life.
http://www.stone-dead.asn.au/movies/life-of-brian/brian-31.htm Received on Fri Nov 13 1998 - 07:07:48 CST