June 1, 2010 at 2:41 pm
Hello,
For now while the DBA's correct some issues causing bad data I need to create a bit of a work around in an SP. We have many records where column F has a 6 digit number. About a third of the time for a specific record type TB.ColumnF is blank. 2/3 of the time for that specific record type TB.ColumnF has six zeros. What I have been asked to do is in my join below if the field is blank sub in 000000 so the record matches a TA.ColumnF row instead of being dropped. I however can not think of any "clean" way to do this so I was hoping others may have ideas.
SELECT *
FROM TableA TA, TableB TB
WHERE
TableA.Name = @Name
AND TA.ColumnF = TB.ColumnF
June 1, 2010 at 2:52 pm
Blank NULL? Or blank zero characters? You can certainly use ISNULL, but if you care about performance more than simplicity, go for the union.
SELECT *
FROM TableA TA, TableB TB
WHERE
TableA.Name = @Name
AND TA.ColumnF = TB.ColumnF
UNION ALL
SELECT *
FROM TableA TA, TableB TB
WHERE
TableA.Name = @Name
AND TA.ColumnF = '000000' AND TB.ColumnF = ''
(if blank is NULL, that final "=''" would be an 'IS NULL')
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy