March 12, 2009 at 6:11 am
I have a table that contains 6 "pseudo" boolean fields, represented as chars '1' and '0'.
As luck would have it, some of these rows have null values, worse, a handful of others have invalid characters. So I would like to preserve valid '1's and '0's, and force invalid or null values to '0'. These fields are joined to another table which defines all possible 63 combinations of these fields. My join statement is currently:
FROM dbo.[Joined All Employees] AS JAE
LEFT OUTER JOIN [XLAT_ERI Codes] AS ERICD
on COALESCE(JAE.[ERI-African-Amer],'0') = ERICD.[Black/AA]
AND COALESCE(JAE.[ERI-Asian],'0') = ERICD.[Asian]
AND COALESCE(JAE.[ERI-Hawaiian-Pac-Island],'0') = ERICD.[NH/PI]
AND COALESCE(JAE.[ERI-Hispanic],'0') = ERICD.[H/L]
AND COALESCE(JAE.[ERI-Native-Amer],'0') = ERICD.[AI/AN]
AND COALESCE(JAE.[ERI-White],'0') = ERICD.[White]
I would like to avoid putting each of these COALESCE statements inside a case, like:
case when COALESCE(JAE.[ERI-African-Amer],'0') = '1' then '1' else '0' end
but am unable to find a "Greater of" operator, or any boolean/masking/bitwise operator that works with chars. Does T-SQL not have one just because the name "Max" was always taken?
I can define a UDF to do the edits but worry about a performance penalty. (I know, I should just try it and time it!) Is there a cleaner way avoid all the additional code?
March 12, 2009 at 10:07 am
SQLServer translates COALESCE in CASE / WHEN, so it would be exactly the same to do:
CASE
WHEN field IS NULL THEN '0'
WHEN field = '0' THEN '0'
WHEN field = '1' THEN '1'
ELSE 'whatever I decide'
END
Obviously it doesn't bring any performance gain.
Is it a matter of performance or code complexity? Why you want to get rid of COALESCE?
Gianluca
-- Gianluca Sartori
March 12, 2009 at 11:03 am
Thanks!
I guess my concern was largely esthetic; just hated to see all those coalesce/case statements cluttering up the code. What I finally settled on doing was hiding the convoluted code in a table returning UDF, which takes the original columns as arguments, and returns a corresponding set of edited/converted columns. I reference that function in a cross apply, and work with the clean columns. Do I pay a penalty? Probably, but it was not noticeable in wall clock time.
I was not award that a COALASCE could be replaced by a case statement with no loss of performance; I should probably use that approach and time it.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply