Re: Multiple datasets in one dataset

From: Dieter Noeth <dnoeth_at_gmx.de>
Date: Sun, 21 Oct 2007 11:20:46 +0200
Message-ID: <fff5l4$p4e$01$1@news.t-online.com>

roman.morokutti_at_googlemail.com wrote:

>> I don't like the SQL standard syntax for JOINs. So, you'll get no
>> argument from me. :)

>
> I don´t like it too. In the real world I have written a conversion
> program which does this on our application on the fly. There the
> appropriate parameters gets defined and the program create either
> the Oracle syntax, or the standard syntax, depending on database
> in use.

Is there really any DBMS (you're application deals with) which doesn't support ANSI joins?
Watch out and check the expected result set, there are differences between ANSI and proprietary outer joins (not only for Oracle but also for e.g. MS SQL and DB2) as soon as you add some search conditions. Whereas if you implement ANSI style the answer set will always be the same on any DBMS.

When you're too lazy, i'll do it for you, but only this time ;-)

SELECT *
FROM Foo A

   LEFT JOIN Foo B
     ON B.WP = A.WP AND B.Curr_Ms = A.Curr_Ms AND B.Value_Group = 21    LEFT JOIN Foo C
     ON C.WP = A.WP AND C.Curr_Ms = A.Curr_Ms AND C.Value_Group = 22    LEFT JOIN Foo D
     ON D.WP = A.WP AND D.Curr_Ms = A.Curr_Ms AND D.Value_Group = 23    LEFT JOIN Foo E
     ON E.WP = A.WP AND E.Curr_Ms = A.Curr_Ms AND E.Value_Group = 24    LEFT JOIN Foo F
     ON F.WP = A.WP AND F.Curr_Ms = A.Curr_Ms AND F.Value_Group = 25 WHERE
   A.Value_Group = 11

Dieter Received on Sun Oct 21 2007 - 04:20:46 CDT