Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Multiple datasets in one dataset
Hi Dieter,
> Is there really any DBMS (you're application deals with) which doesn't
> support ANSI joins?
Yes. Oracle 9. But I am with you that most modern DBMSs are currently
support the standard. But wouldn´t the standard be much prettier
adopting
the Oracle style? (Never mind. I am just too used to it ;-))
> 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.
Interesting.
> 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
Thanks. :-)
Regards
Roman
Received on Tue Oct 23 2007 - 04:03:39 CDT