using CASE WHEN in select statement

Question

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'

a1ex07 · Accepted Answer · 2011-04-20 19:59:39Z

3

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

edited Apr 20, 2011 at 19:59

answered Apr 20, 2011 at 19:49

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

userstackoverflow Over a year ago

I can run the query with the error but the gridview(where the data is displayed) has empty cell when the value is Null but can see both True and False in records!!

userstackoverflow Over a year ago

Thank you a1ex07. The second case statement worked. Really appreciate it.

Joe Stefanelli · Accepted Answer · 2011-04-20 19:49:48Z

1

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

answered Apr 20, 2011 at 19:49

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

1 Comment

userstackoverflow Over a year ago

I can run the query with the error but the gridview(where the data is displayed) has empty cell when the value is Null and can see both True and False in records in the gridview!! any idea why?

TBohnen.jnr · Accepted Answer · 2011-04-20 19:51:20Z

1

when COALESCE(prog.has_post_calc_screen,''False'') = ''False''then ''False''

answered Apr 20, 2011 at 19:51

TBohnen.jnr

5,1191 gold badge22 silver badges26 bronze badges

1 Comment

JNK Over a year ago

+1 beat me to it - just use COALESCE since the only case you want to change is a NULL

Collectives™ on Stack Overflow

using CASE WHEN in select statement

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related