Interesting article. I'm glad I always use the newer syntax. To the poster who said the problem was that the problem was not knowing which part of the where to apply, I would have to disagree. It is something more than that, although I don't know what. For example, I tried the following:
SELECT testnull =
CASE
WHEN tmp.Rats_filename IS NULL THEN 'NULL '
WHEN tmp.Rats_filename IS NOT NULL THEN 'NOT NULL'
ELSE 'OTHER'
END
, *
FROM
(SELECT *
FROM UUG_APP_DVLP.dbo.[IIAFeedTransaction] s
, UUG_APP_DVLP.dbo.[RATSIIAFeedTransaction] o
WHERE S.IIATransactionId *= substring(rats_filename,1+
patindex ('%{________-____-____-____-
____________}%',rats_filename),36)
)TMP
WHERE tmp.Rats_filename IS NOT NULL
ORDER BY 2
For the above, there should be no problem with what to join together. The second WHERE should work properly, and I would expect it to work the same as the CASE statement. But it doesn't!!!. See results below:
Testnull | IIATransactionId |
| RATS_FILENAME | DATE_ENTERED |
NULL | 9B33A776-408B-4928-AE2A-0FF43995DE12 | 1 | NULL | NULL |
NULL | 8EEA51CE-D87A-4F76-B9C1-7AD11532D444 | 0 | NULL | NULL |
NULL | 5D31FD56-7C0B-408B-83A5-864B7BD35ADC | 1 | NULL | NULL |
NOT NULL | 365A0FD8-5042-4297-A082-8F5B11450AF4 | 1 | CISF-{260021}-{365A0FD8-5042-4297-A082-8F5B11450AF4}-{7A050246-59F3-4E72-BFD2-40EB183B7D3C} | 2006-05-03 13:46:21.470 |
NOT NULL | 16706611-C94D-4FBC-8F4E-9077C3B9E697 | 1 | CISF-{260346}-{16706611-C94D-4FBC-8F4E-9077C3B9E697}-{73916523-EC71-4989-BE99-1EDB507D67DF} | 2006-05-03 13:46:21.470 |
NOT NULL | 55DD6703-9693-45E2-A339-987066EA2864 | 2 | RA-1-{259590}-{55DD6703-9693-45E2-A339-987066EA2864}-{EF41CFCE-20BA-4D2D-A4C8-121BFC7DAEE3} | 2006-05-03 13:46:21.470 |
NULL | A721BD38-9C76-4C3A-A7C5-9CC0227F90F4 | 0 | NULL | NULL |
NOT NULL | F4849137-6454-46E5-9811-A6985A189249 | 1 | RA-2-{219960}-{F4849137-6454-46E5-9811-A6985A189249}-{F7267BD6-62CB-4379-8533-C413DD5EA402} | 2006-05-03 13:46:21.470 |
Moral of this story is: Stay with the new syntax.