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.
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' ENDFROM TDP, Activity AWHERE 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%' |
|
|
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(???). |
|
|
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_CODEFROM dbo.ActivityWHERE (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_TYPE1333392 | 01/06/2010 | 12/01/2009 | 11/30/2010 | 30.0000 | 2010 | CHAPT | A | DUES1333392 | 01/06/2010 | 12/01/2009 | 11/30/2010 | 30.0000 | 2010 | DUES | A | DUES1333392 | 11/02/2010 | NULL | NULL | 0.0000 | Z999 | | INS | 1333392 | 11/03/2010 | 12/01/2010 | 11/30/2011 | 30.0000 | 2011 | CHAPT | A | DUES1333392 | 11/03/2010 | 12/01/2010 | 11/30/2011 | 30.0000 | 2011 | DUES | A | DUES1343376 | 11/03/2010 | 10/01/2010 | 09/30/2011 | 30.0000 | 2010 | CHAPT | A | DUES1343376 | 11/03/2010 | 10/01/2010 | 09/30/2011 | 30.0000 | 2010 | DUES | A | DUESFor 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. |
|
|
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 zand the outer join expression: Table1 LEFT OUTER JOIN Table2 ON Table1.a = Table2.a <== join condition AND Table2.c = 't'; <== single table conditionWe 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 JOIN3) 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 NULLthe 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 NULLThe 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 250and 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 NULLThen I apply the (qty < 200) predicate and get Suppliers LOJ SupParts supno supno partno qty =================== S1 S1 P1 100 S2 S2 P1 100Doing it in the opposite order Suppliers LOJ SupParts supno supno partno qty =================== S1 S1 P1 100 S2 S2 P1 100 S3 NULL NULL NULLSybase 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 PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
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_CODEFROM (SELECT TDP.ID, TDP.TRANSACTION_DATE, TDP.EFFECTIVE_DATE, TDP.SOURCE_CODE, TDP.UF_4 FROM TDP) TDPLEFT OUTER JOIN (SELECT A.ID, A.SOURCE_CODE, A.UF_4, A.THRU_DATE FROM Activity AWHERE 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 |
|
|
|
|
|
|
|