Migrating from SQL Server

[Pablo Etcheverry]

I'm migrating a huge application wrote for sql server, this software
offers the option for al user to write their own reports. The report
editor was developed many years ago, and it uses the TransactSQL
native syntax for LEFT OUTER JOINS (*=).
The problem is, that syntax obtains diferents data sets than the ANSI
form (FROM T1 LEFT OUTER JOIN T2).

Sample:
Consider that the t3.c column does not contains the 999 value

When execute this sentence:

Select * from T1, T2 LEFT OUTER JOIN T3 ON T2.C = T3.C where T1.B =
T2.B and t3.c = 999

You will obtain
A B C B C D
C D E
----------- ----------- ----------- ----------- -----------
----------- ----------- ----------- -----------

(0 row(s) affected)

but, if you write it in the native transactsql syntax:

Select * from T1,T2,T3 where T1.B=T2.B and T2.C*=T3.C and t3.c = 999

you will obtain

A B C B C D
C D E
----------- ----------- ----------- ----------- -----------
----------- ----------- ----------- -----------
1 1 1 1 1 1
NULL NULL NULL
2 2 2 2 2 2
NULL NULL NULL

(2 row(s) affected)

So there is an inconsitency in the data set, but i HAVE to preserve
this behavior.

Have anyone any idea on what i have to do on DB2 to obtain the same
result?

Thanks

[Tokunaga T.]

pa***@cine.com (Pablo Etcheverry) wrote in message:

I'm migrating a huge application wrote for sql server, this software
offers the option for al user to write their own reports. The report
editor was developed many years ago, and it uses the TransactSQL
native syntax for LEFT OUTER JOINS (*=).
The problem is, that syntax obtains diferents data sets than the ANSI
form (FROM T1 LEFT OUTER JOIN T2).

Sample:
Consider that the t3.c column does not contains the 999 value

When execute this sentence:

Select * from T1, T2 LEFT OUTER JOIN T3 ON T2.C = T3.C where T1.B =
T2.B and t3.c = 999

You will obtain
A B C B C D
C D E
----------- ----------- ----------- ----------- -----------
----------- ----------- ----------- -----------

(0 row(s) affected)

but, if you write it in the native transactsql syntax:

Select * from T1,T2,T3 where T1.B=T2.B and T2.C*=T3.C and t3.c = 999

you will obtain

A B C B C D
C D E
----------- ----------- ----------- ----------- -----------
----------- ----------- ----------- -----------
1 1 1 1 1 1
NULL NULL NULL
2 2 2 2 2 2
NULL NULL NULL

(2 row(s) affected)

You should warite predicate "t3.c = 999" in ON clause. Like this:
Select *
from T1
, T2
LEFT OUTER JOIN
T3
ON T2.C = T3.C
and t3.c = 999
where T1.B = T2.B

Reason:
If you wrote the predicate in WHERE clause, the condition will be
applied for JOINed result.
For your sample:
After JOINed tables you will get 2 rows with t3.c IS NULL.
Predicate "t3.c = 999" will be applied for this result. Both rows
couldn't satisfy the condition, then they will be removed from the
final result.