I am using following for select statement with case. The column to which the CASE is applied has True, False and Null values. I am trying to display True for True and False for both False and Null. But it give me an error message saying:
"Conversion failed when converting the varchar value 'Null' to data type bit."
This is my query:
case
when prog.has_post_calc_screen = ''True'' then ''True''
when prog.has_post_calc_screen = ''False''then ''False''
when prog.has_post_calc_screen = ''Null'' then ''False''
End as Referal_ID,
Input which I am giving is:
'1','ALL', 'ALL'
Instead of when prog.has_post_calc_screen = ''Null''
write when prog.has_post_calc_screen IS NULL
THEN ... (You might also write ELSE ...
because you have already processed true and false, so the only possible value is NULL).
UPDATE You can also write just
case
when prog.has_post_calc_screen = true then 'True'
else 'False'
END ....
You cannot test for a NULL value with an =
. You must use IS NULL
instead, so change the one line of your CASE
:
when prog.has_post_calc_screen IS NULL then ''False''
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With