Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Need Help on Non ANSI Joins Rewrite

Author  Topic 

eman0827
Starting Member

4 Posts

Posted - 2010-12-30 : 12:30:40
Hello,

I have a query from SQL Server 2000 that needs to be rewritten for SQL Server 2008. The following query looks like this:

SELECT TDP.ID, TDP.TRANSACTION_DATE, A.THRU_DATE, TDP.EFFECTIVE_DATE, CONDITION_CODE =
CASE WHEN datediff(day, A.THRU_DATE, TDP.EFFECTIVE_DATE)
= 1 THEN 'R' ELSE 'N' END
FROM TDP, Activity A
WHERE TDP.ID *= A.ID AND A.SOURCE_CODE =* CONVERT(varchar(10), CONVERT(int, TDP.SOURCE_CODE) - 1)
AND A.UF_4 =* TDP.UF_4 AND A.OTHER_CODE IN ('DUES',
'MMS') AND A.ACTIVITY_TYPE = 'DUES' AND A.ACTION_CODES NOT LIKE 'R%'

Any ideas? Thanks in advance!

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-12-30 : 12:57:33
Try this:

SELECT TDP.ID, TDP.TRANSACTION_DATE, A.THRU_DATE, TDP.EFFECTIVE_DATE,
CASE WHEN datediff(day, A.THRU_DATE, TDP.EFFECTIVE_DATE) = 1
THEN 'R'
ELSE 'N'
END AS CONDITION_CODE
FROM TDP
LEFT OUTER JOIN
Activity AS A
ON A.ID = TDP.ID
AND A.SOURCE_CODE = CONVERT(VARCHAR(10), CONVERT(INT, TDP.SOURCE_CODE) - 1)
AND A.UF_4 = TDP.UF_4
WHERE A.OTHER_CODE IN ('DUES', 'MMS')
AND A.ACTIVITY_TYPE = 'DUES'
AND A.ACTION_CODES NOT LIKE 'R%'
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-30 : 13:23:29
quote:
Originally posted by malpashaa

Try this:

SELECT TDP.ID, TDP.TRANSACTION_DATE, A.THRU_DATE, TDP.EFFECTIVE_DATE,
CASE WHEN datediff(day, A.THRU_DATE, TDP.EFFECTIVE_DATE) = 1
THEN 'R'
ELSE 'N'
END AS CONDITION_CODE
FROM TDP
LEFT OUTER JOIN
Activity AS A
ON A.ID = TDP.ID
AND A.SOURCE_CODE = CONVERT(VARCHAR(10), CONVERT(INT, TDP.SOURCE_CODE) - 1)
AND A.UF_4 = TDP.UF_4
WHERE A.OTHER_CODE IN ('DUES', 'MMS')
AND A.ACTIVITY_TYPE = 'DUES'
AND A.ACTION_CODES NOT LIKE 'R%'


Just for reference, by putting those predicates in the where clause you have, affectivly, turned the outer join into an inner join.

Without sample data and expected output it's hard to make suggestions. I looks like the original query is doing a left and a right join to the same table(???).
Go to Top of Page

eman0827
Starting Member

4 Posts

Posted - 2010-12-30 : 14:15:40
Thanks for the replies.

Unfortunately, malpashaa's solution didn't return the same results as the original query. It returned 304 records where it's supposed to return 367 (according to the original query). The TDP is actually a view from the same Activity table. It's supposed to grab all the records from the TDP with their corresponding Activity records on the Activity table.


The TDP view is as follows:

SELECT ID, TRANSACTION_DATE, EFFECTIVE_DATE, THRU_DATE, UF_4, SOURCE_CODE
FROM dbo.Activity
WHERE (ACTIVITY_TYPE = 'DUES') AND (PRODUCT_CODE IN ('CFBF', ' MONTHLY_NEW', 'MONTHLY_EXIST', 'MONTHLY_REN', 'ADMIN_FEE')) AND
(UF_3 = ' ')

Just an example using two records, here's a result set on the TDP view:
ID | TRANSACTION_DATE | EFFECTIVE_DATE | THRU_DATE | UF_4 | SOURCE_CODE |
1333392 | 11/03/2010 | 12/01/2010 | 11/30/2010 | 30 | 2011 |
1343376 | 11/03/2010 | 10/01/2010 | 09/30/2010 | 30 | 2010 |

Now here's their corresponding Activity records:
ID | TRANSACTION_DATE | EFFECTIVE_DATE | THRU_DATE | UF_4 | SOURCE_CODE | OTHER_CODE | ACTION_CODES | ACTIVITY_TYPE
1333392 | 01/06/2010 | 12/01/2009 | 11/30/2010 | 30.0000 | 2010 | CHAPT | A | DUES
1333392 | 01/06/2010 | 12/01/2009 | 11/30/2010 | 30.0000 | 2010 | DUES | A | DUES
1333392 | 11/02/2010 | NULL | NULL | 0.0000 | Z999 | | INS |
1333392 | 11/03/2010 | 12/01/2010 | 11/30/2011 | 30.0000 | 2011 | CHAPT | A | DUES
1333392 | 11/03/2010 | 12/01/2010 | 11/30/2011 | 30.0000 | 2011 | DUES | A | DUES
1343376 | 11/03/2010 | 10/01/2010 | 09/30/2011 | 30.0000 | 2010 | CHAPT | A | DUES
1343376 | 11/03/2010 | 10/01/2010 | 09/30/2011 | 30.0000 | 2010 | DUES | A | DUES

For some reason, the ID 1333392 shows up on the results but not 1343376. I realize it could also be these where clauses that needs fixing. Looking over the query that malpashaa posted, it gave me some ideas that I have yet to try. Also, thanks for the insight Lamprey.
Go to Top of Page

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-12-30 : 16:56:58
This code looks old! CONVERT instead of CAST! The silly Germanic "id" that newbies used to mimic pointer chains. I will bet the dates are really DATETIME :)

First, let's get the =* over to *= so we can see the preserved table. That is important -- in this syntax, only one table cam be preserved and the only test is equality.

SELECT TDP.magic_generic_id,
TDP.transaction_date, A.thru_date, TDP.effective_date,
CASE WHEN DATEDIFF(DAY, A.thru_date, TDP.effective_date) = 1
THEN 'R' ELSE 'N' END
AS condition_code
FROM TDP, Activities AS A
WHERE TDP.magic_generic_id *= A.magic_generic_id
AND CAST(CAST(TDP.source_code AS INTEGER)-1 AS VARCHAR(10)) *= A.source_code
AND TDP.uf_4 *= A.uf_4
AND A.other_code IN ('DUES', 'MMS')
AND A.activity_type = 'DUES'
AND A.action_code NOT LIKE 'R%';

Okay, TDP is the preserved table. Now, alter the table so that the source_code is one and only one data type; it looks like INTEGER is best. Don't do math on the preserved side -- I cannot remember why, but that was the heuristic.

SELECT TDP.magic_generic_id,
TDP.transaction_date, A.thru_date, TDP.effective_date,
CASE WHEN DATEDIFF(DAY, A.thru_date, TDP.effective_date) = 1
THEN 'R' ELSE 'N' END
AS condition_code
FROM TDP, Activities AS A
WHERE TDP.magic_generic_id *= A.magic_generic_id
AND TDP.source_code *= A.source_code +1
AND TDP.uf_4 *= A.uf_4
AND A.other_code IN ('DUES', 'MMS')
AND A.activity_type = 'DUES'
AND A.action_code NOT LIKE 'R%';

Both Sybase and Oracle had this "extended equality" outer join. But they did it differently! One did the outer tests first then applied the SARGs and non-outer jons. I cannot remember this code museum and I am an old fart! Here is my totally wild guess:

SELECT TDP.magic_generic_id,
TDP.transaction_date, A.thru_date, TDP.effective_date,
CASE WHEN DATEDIFF(DAY, A.thru_date, TDP.effective_date) = 1
THEN 'R' ELSE 'N' END
AS condition_code
FROM (SELECT magic_generic_id, transaction_date, effective_date
FROM TDP)
LEFT OUTER JOIN
(SELECT magic_generic_id, source_code, uf_4,thru_date
FROM Activities
WHERE A.other_code IN ('DUES', 'MMS')
AND A.activity_type = 'DUES'
AND A.action_code NOT LIKE 'R%') AS A
ON TDP.magic_generic_id = A.magic_generic_id
AND TDP.source_code = A.source_code +1
AND TDP.uf_4 = A.uf_4;


I would start over from scratch with the current specs; translations are not usually effective.

Here is how OUTER JOINs work in Standard SQL. Assume you are given:

Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z

and the outer join expression:

Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition

We call Table1 the "preserved table" and Table2 the "unpreserved table" in the query. What I am going to give you is a little different, but equivalent to the ANSI/ISO standards.

1) We build the CROSS JOIN of the two tables. Scan each row in the result set.

2) If the predicate tests TRUE for that row, then you keep it. You also remove all rows derived from it from the CROSS JOIN

3) If the predicate tests FALSE or UNKNOWN for that row, then keep the columns from the preserved table, convert all the columns from the unpreserved table to NULLs and remove the duplicates.

So let us execute this by hand:

Let @ = passed the first predicate
Let * = passed the second predicate

Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
-----------------------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
-----------------------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
-----------------------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL

the final results:

Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL

The basic rule is that every row in the preserved table is represented in the results in at least one result row.

There are limitations and very serious problems with the extended equality version of an outer join used in some diseased mutant products. Consider the two Chris Date tables

Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250

and let's do an extended equality outer join like this:

SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;

If I do the outer first, I get:

Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL

Then I apply the (qty < 200) predicate and get

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100

Doing it in the opposite order

Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL

Sybase did it one way, Oracle did it the other and Centura (nee Gupta) let you pick which one -- the worst of both non-standard worlds! In Standard SQL, you have a choice and can force the order of execution. Either do the predicates after the join ...

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;

... or do it in the joining:

SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;

Another problem is that you cannot show the same table as preserved and unpreserved in the extended equality version, but it is easy in Standard SQL. For example to find the students who have taken Math 101 and might have taken Math 102:

SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;


--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Go to Top of Page

eman0827
Starting Member

4 Posts

Posted - 2010-12-30 : 19:44:37
Thanks for the reply jcelko. Yes, the code is old (I think prior to SQL Server 2000 or even older than its previous version) and all your assumptions were correct. I wasn't expecting quite a detailed and very useful response. Just goes to show how little I know of SQL. Learn something new everyday. Thanks again to everyone for their responses.

Anyways, this did the trick:

SELECT TDP.ID, TDP.TRANSACTION_DATE, A.THRU_DATE, TDP.EFFECTIVE_DATE, TDP.SOURCE_CODE,
TDP.UF_4,
CASE WHEN DATEDIFF(Day, A.THRU_DATE, TDP.EFFECTIVE_DATE)=1 THEN 'R' ELSE 'N' END as
CONDITION_CODE
FROM
(SELECT TDP.ID, TDP.TRANSACTION_DATE, TDP.EFFECTIVE_DATE, TDP.SOURCE_CODE, TDP.UF_4 FROM TDP) TDP
LEFT OUTER JOIN (SELECT A.ID, A.SOURCE_CODE, A.UF_4, A.THRU_DATE FROM Activity A
WHERE A.OTHER_CODE IN ('DUES', 'MMS') AND A.ACTIVITY_TYPE='DUES'
AND A.ACTION_CODES NOT LIKE 'R%') AS A ON TDP.ID=A.ID AND TDP.SOURCE_CODE=A.SOURCE_CODE+1
AND TDP.UF_4=A.UF_4
ORDER BY TDP.ID
Go to Top of Page
   

- Advertisement -