Using Case Function to make NULL values appear as blank

Question

I keep getting error that my Case Function is incorrect. My goal is to use the Case Function to make every NULL or '0' values appear as blank. Incompatible data types in case statement is the prompt that I get when running it in report studio. Example of the table would be this. WCPDOD is an Integer, WCPDOD 9int,null)

WCPDOD     POLICY   CLAIM NUMBER
19741020   2001A    123456N000040
20040101   2003A    456789001531
20021220   00594        123456
20040507   2003A    2222043621
20040517   2003I    90043625

CASE 
WHEN [WCPDOD] = '0' THEN ' '
WHEN [WCPDOD] IS NULL THEN ' '
ELSE [WCPDOD]
END

Answer 1

Since WCPDOD is an integer, you shouldn't compare it to string literals such as '0', but to numeric literals, such as 0. More importantly, different branches of your case statement return different types - the first two return strings (the ' ' literal) and the else branch returns an integer. You should cast it to a string:

CASE 
WHEN [WCPDOD] = 0 THEN ' '
WHEN [WCPDOD] IS NULL THEN ' '
ELSE CAST ([WCPDOD] AS VARCHAR(40))
END