Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

using CASE WHEN in select statement

Tags:

sql

case

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'
like image 485
userstackoverflow Avatar asked Apr 20 '11 19:04

userstackoverflow


2 Answers

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 ....
like image 79
a1ex07 Avatar answered Oct 12 '22 07:10

a1ex07


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''
like image 37
Joe Stefanelli Avatar answered Oct 12 '22 07:10

Joe Stefanelli