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 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply