TSQL - Case - Values in ?

Question

Is there a way we can specify values in for a case statement? The below statement doesn't execute because it thinks 53,57,82,83 etc are columns.. Is there a work around.. i googled up but found nothing that say you can't use IN case-when expression..

select 
x =
case 
    when xvalue in ([52],[57],[82],[83]) 
        then "xvalue"
    when yvalue in ([01],[02],[11]) 
        then "yvalue"
    else
        'NULL'
    end
from xyztable

Answer 1

Don't put brackets around the numbers.

Answer 2

Try this:

select 
case 
    when xvalue in (52,57,82,83) 
        then "xvalue"
    when yvalue in (01,02,11) 
        then "yvalue"
    else
        'NULL'
    end as 'x'

If you want to use the x, which I am assuming is going to be a variable you will want to define it like this:

DECLARE @x int

select 
@x = case 
    when xvalue in (52,57,82,83) 
        then "xvalue"
    when yvalue in (01,02,11) 
        then "yvalue"
    else
        'NULL'
    end

Answer 3

Use:

SELECT x = CASE 
             WHEN t.xvalue IN (52, 57, 82, 83) THEN 'xvalue'
             WHEN t.yvalue IN (01, 02, 11) THEN 'yvalue'
             ELSE NULL
           END
  FROM TABLE t

Assuming you want the value from the column, use:

SELECT x = CASE 
             WHEN t.xvalue IN (52, 57, 82, 83) THEN t.xvalue
             WHEN t.yvalue IN (01, 02, 11) THEN t.yvalue
             ELSE NULL
           END
  FROM TABLE t

You realize that if both xvalue and yvalue are in the groups, only the xvalue will be displayed?

Answer 4

Sure, if xvalue is a numeric column, just remove the brackets. Then it should work fine. The brackets tell the database server that something is a database object, so remove them so they are treated like a literal.

Answer 5

On t-sql, [] identifiers are for quoting symbols like column names - you can have spaces and all sorts of odd characters in column names if you really want to. Use '' quotes to quote string literals.

Answer 6

The reason your query doesn't work is that it is written incorrectly. Look at this:

in ([01],[02],[11])  

Putting [] around your values means you want them treated as column names. If you remove the quotes, then that part will work. Also look at this:

then "xvalue" 

you need single quotes here.

else 'NULL'

Here, do you want the value to be NULL or the string "NULL"? The way you have written it, it will be the string "NULL"

To make it NULL value, write it like this:

else NULL

Here is the whole thing again, written correctly:

select    
x =   
case    
    when xvalue in (52,57,82,83)    
        then 'xvalue'
    when yvalue in (01,02,11)    
        then 'yvalue'
    else 'NULL'   
    end   
from xyztable