Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

TSQL and case when with multiple whens?

Tags:

tsql

case-when

I have several conditions and the result for those should be the same. I searched the net and found stuff like this:

CASE ProductLine
     WHEN 'R' THEN 'Road'
     WHEN 'M' THEN 'Mountain'
     WHEN 'T' THEN 'Touring'
     WHEN 'S' THEN 'Other sale items'
     ELSE 'Not for sale'
  END

That's nice, but not what I need, for me its more like R,M,T and S all have the same result and A,B,C,D for example don't have. How would I do this? I cant connect with OR, or at least I did not manage to :). Something like this maybe?

CASE ProductLine
     WHEN 'R' OR 'M' OR ... THEN 'Road'
     ELSE 'Not for sale'
  END
like image 970
grady Avatar asked Oct 17 '11 10:10

grady


People also ask

How do you write multiple conditions in SQL?

The SQL AND & OR operators are used to combine multiple conditions to narrow data in an SQL statement. These two operators are called as the conjunctive operators. These operators provide a means to make multiple comparisons with different operators in the same SQL statement.

Can we use AND condition in CASE statement in SQL?

CASE must include the following components: WHEN , THEN , and END . ELSE is an optional component. You can make any conditional statement using any conditional operator (like WHERE ) between WHEN and THEN . This includes stringing together multiple conditional statements using AND and OR .

Can we use multiple conditions in WHERE clause in SQL?

You can specify multiple conditions in a single WHERE clause to, say, retrieve rows based on the values in multiple columns. You can use the AND and OR operators to combine two or more conditions into a compound condition. AND, OR, and a third operator, NOT, are logical operators.


1 Answers

Change to a "searched" CASE expression. You have a "simple" CASE expression above

CASE
   WHEN  ProductLine IN ('R', 'M', ...) THEN 'Road'
   ELSE 'Not for sale'
END

From the MSDN link above:

Simple CASE expression: 
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 

Searched CASE expression:
CASE
     WHEN Boolean_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END
like image 68
gbn Avatar answered Jan 03 '23 11:01

gbn