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
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