OR is not supported with CASE Statement in SQL Server

Question

The OR operator in the WHEN clause of a CASE statement is not supported. How can I do this?

CASE ebv.db_no 
    WHEN 22978 OR 23218 OR 23219 THEN 'WECS 9500' 
    ELSE 'WECS 9520' 
END as wecs_system

SQL Standard allows for multiple values: https://stackoverflow.com/a/54562580/5070879 — Lukasz Szozda, Oct 10 '19 at 20:12

score 1112 · Accepted Answer · answered Mar 30 '11 at 14:47

1112

That format requires you to use either:

CASE ebv.db_no 
  WHEN 22978 THEN 'WECS 9500' 
  WHEN 23218 THEN 'WECS 9500'  
  WHEN 23219 THEN 'WECS 9500' 
  ELSE 'WECS 9520' 
END as wecs_system

Otherwise, use:

CASE  
  WHEN ebv.db_no IN (22978, 23218, 23219) THEN 'WECS 9500' 
  ELSE 'WECS 9520' 
END as wecs_system

answered Mar 30 '11 at 14:47

OMG Ponies

325,700
82
523
502

9

For the second case, why does only 'IN' work and not '='? – Han Jun 13 '16 at 14:53
27

`=` would work if you were comparing to a single value. However, `(22978, 23218, 23219)` is an array and `IN` is necessary to match only one of the values. – LdTrigger Nov 24 '16 at 14:49
2

This really stinks- t-sql can't handle an "or" in a case statement. Come on Microsoft time to grow up from the toy database status. – Rich Bianco Jan 11 '18 at 17:46
2

"can't handle an "or" in a case statement".. hmmm.... i dont think ive ever seen a switch accept an "or" in any language. would seem to defeat the purpose of a switch. what languages accept "or" in a case? – Heriberto Lugo Jan 24 '18 at 15:32
2

@Heriberto Lugo I don't know how many language you know but there are at least a few. VB.NET and C# can use them with simple comma separation. It doesn't defeat anything as it will save you from repeating the same code in multiple case for nothing. – Johnny Prescott Mar 01 '18 at 14:49
@JohnnyPrescott i use both of those. but i didnt think that was an actual "or". but more of a shorthand to associate them with the same case. – Heriberto Lugo Mar 01 '18 at 14:58
@Heriberto Lugo Which pretty much result in the same thing. :) – Johnny Prescott Mar 01 '18 at 14:59
@JohnnyPrescott - results -> not means. they allow you to fall through if you dont break. but maybe you're right. as long as the result is achieved. now you've given me something to thinks about.. thanks.. lol – Heriberto Lugo Mar 01 '18 at 15:06
The closest would be shell: `case $db_no in 22978|23218|23219) echo 'WECS 9500' ;; *) echo 'WECS 9520' ;; esac` – Barmar Mar 03 '20 at 19:34
1

@RichBianco T-SQL most certainly can handle OR in a case statement as made apparent by both options in this answer and furthermore in the next highest voted answer where it truly is used. Also OR is more of a concept of a functional language rather than a relational one. So don't hate the syntax, hate the game. – J.D. Dec 21 '20 at 04:50
This answer and these comments were largely a waste of time. TLDR: OR is definitely something you can use in SQL Case statements; see next best voted answer. – egmfrs Jun 27 '22 at 13:34

score 267 · Answer 2 · edited Apr 21 '14 at 07:58

267

CASE
  WHEN ebv.db_no = 22978 OR 
       ebv.db_no = 23218 OR
       ebv.db_no = 23219
  THEN 'WECS 9500' 
  ELSE 'WECS 9520' 
END as wecs_system

edited Apr 21 '14 at 07:58

Amarnath Balasubramanian

9,300
8
34
62

answered Apr 05 '12 at 03:31

Darren

2,687
1
13
2

40

upvoted - this response does add value. It more closely fits the OP's question, and if you want to nest some CASE-WHENS this syntax reduces the necessary code considerably. – Matt Kemp Jun 07 '12 at 04:00
1

@Leigh I appreciate this answer. It is nice to have all the different formats in one thread and makes it more usable as a reference. – Jason Wheeler Apr 25 '14 at 08:22
3

@Bigwheels - Wow.. this was a while ago. I probably disagreed because, logically, it is exactly the same as [other responses](http://stackoverflow.com/a/5487918/104223). That said, you and Matt make valid points. If the question was "what is the correct syntax using OR *only*", this provides an answer. However, if "reducing the syntax necessary" was the goal, the [accepted response](http://stackoverflow.com/a/5487936/104223) is more compact. BTW, it is not a slam on Darren's answer, which is perfectly valid. Just my $0.02 :) – Leigh Apr 25 '14 at 13:24
4

using `IN` keyword is much better way – Sagar Naliyapara Apr 25 '17 at 11:38

score 59 · Answer 3 · edited Sep 11 '20 at 21:37

59

CASE WHEN ebv.db_no IN (22978, 23218, 23219) THEN 'WECS 9500' 
 ELSE 'WECS 9520' 
END as wecs_system

edited Sep 11 '20 at 21:37

Osama Rizwan

615
1
7
19

answered Mar 30 '11 at 14:47

Cade Roux

88,164
40
182
265

score 55 · Answer 4 · edited Jan 14 '17 at 09:41

You can use one of the expressions that WHEN has, but you cannot mix both of them.

WHEN when_expression

Is a simple expression to which input_expression is compared when the simple CASE format is used. when_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.
WHEN Boolean_expression

Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

You could program:

1.

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

2.

    CASE
            WHEN ListPrice =  0 THEN 'Mfg item - not for resale'
            WHEN ListPrice < 50 THEN 'Under $50'
            WHEN ListPrice >= 50 and ListPrice < 250 THEN 'Under $250'
            WHEN ListPrice >= 250 and ListPrice < 1000 THEN 'Under $1000'
            ELSE 'Over $1000'
          END

But in any case you can expect that the variable ranking is going to be compared in a boolean expression.

See CASE (Transact-SQL) (MSDN).

score 39 · Answer 5 · edited Jan 14 '17 at 14:23

There are already a lot of answers with respect to CASE. I will explain when and how to use CASE.

You can use CASE expressions anywhere in the SQL queries. CASE expressions can be used within the SELECT statement, WHERE clauses, Order by clause, HAVING clauses, Insert, UPDATE and DELETE statements.

A CASE expression has the following two formats:

Simple CASE expression
```
CASE expression
WHEN expression1 THEN Result1
WHEN expression2 THEN Result2
ELSE ResultN
END
```
This compares an expression to a set of simple expressions to find the result. This expression compares an expression to the expression in each WHEN clause for equivalency. If the expression within the WHEN clause is matched, the expression in the THEN clause will be returned.

This is where the OP's question is falling. 22978 OR 23218 OR 23219 will not get a value equal to the expression i.e. ebv.db_no. That's why it is giving an error. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.
Searched CASE expressions
```
CASE
WHEN Boolean_expression1 THEN Result1
WHEN Boolean_expression2 THEN Result2
ELSE ResultN
END
```
This expression evaluates a set of boolean expressions to find the result. This expression allows comparison operators, and logical operators AND/OR with in each Boolean expression.

1.SELECT statement with CASE expressions

--Simple CASE expression: 
SELECT FirstName, State=(CASE StateCode
 WHEN 'MP' THEN 'Madhya Pradesh' 
 WHEN 'UP' THEN 'Uttar Pradesh' 
 WHEN 'DL' THEN 'Delhi' 
 ELSE NULL 
 END), PayRate
FROM dbo.Customer

-- Searched CASE expression:
SELECT FirstName,State=(CASE 
 WHEN StateCode = 'MP' THEN 'Madhya Pradesh' 
 WHEN StateCode = 'UP' THEN 'Uttar Pradesh' 
 WHEN StateCode = 'DL' THEN 'Delhi' 
 ELSE NULL 
 END), PayRate
FROM dbo.Customer

2.Update statement with CASE expression

-- Simple CASE expression: 
UPDATE Customer 
SET StateCode = CASE StateCode
 WHEN 'MP' THEN 'Madhya Pradesh' 
 WHEN 'UP' THEN 'Uttar Pradesh' 
 WHEN 'DL' THEN 'Delhi' 
 ELSE NULL 
 END 

-- Simple CASE expression: 
UPDATE Customer 
SET StateCode = CASE 
 WHEN StateCode = 'MP' THEN 'Madhya Pradesh' 
 WHEN StateCode = 'UP' THEN 'Uttar Pradesh' 
 WHEN StateCode = 'DL' THEN 'Delhi' 
 ELSE NULL 
 END

3.ORDER BY clause with CASE expressions

-- Simple CASE expression: 
SELECT * FROM dbo.Customer
ORDER BY 
 CASE Gender WHEN 'M' THEN FirstName END Desc,
 CASE Gender WHEN 'F' THEN LastName END ASC

-- Searched CASE expression: 
SELECT * FROM dbo.Customer
ORDER BY 
 CASE WHEN Gender='M' THEN FirstName END Desc,
 CASE WHEN Gender='F' THEN LastName END ASC

4.Having Clause with CASE expression

-- Simple CASE expression: 
SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
FROM dbo.Customer
GROUP BY StateCode,Gender,FirstName
HAVING (MAX(CASE Gender WHEN 'M' 
 THEN PayRate 
 ELSE NULL END) > 180.00
 OR MAX(CASE Gender WHEN 'F' 
 THEN PayRate 
 ELSE NULL END) > 170.00)

-- Searched CASE expression: 
SELECT FirstName ,StateCode,Gender, Total=MAX(PayRate)
FROM dbo.Customer
GROUP BY StateCode,Gender,FirstName
HAVING (MAX(CASE WHEN Gender = 'M' 
 THEN PayRate 
 ELSE NULL END) > 180.00
 OR MAX(CASE WHEN Gender = 'F' 
 THEN PayRate 
 ELSE NULL END) > 170.00)

Hope this use cases will help someone in future.

Source

score 34 · Answer 6 · edited Sep 22 '14 at 08:29

34

Try

CASE WHEN ebv.db_no IN (22978,23218,23219) THEN 'WECS 9500' ELSE 'WECS 9520' END

edited Sep 22 '14 at 08:29

davmos

9,324
4
40
43

answered Mar 30 '11 at 14:46

JNK

63,321
15
122
138

score 28 · Answer 7 · edited Oct 13 '15 at 11:24

28

SELECT
  Store_Name,
  CASE Store_Name
    WHEN 'Los Angeles' THEN Sales * 2
    WHEN 'San Diego' THEN Sales * 1.5
    ELSE Sales
    END AS "New Sales",
  Txn_Date
FROM Store_Information;

edited Oct 13 '15 at 11:24

Kristian

1,099
1
11
24

answered Feb 25 '15 at 12:43

Archu

281
3
2

1

Upvoting because of the inclusion of an `ELSE Sales` field, which returns the default value if not else included within a case statement, appropriate for business queries. – FoxDeploy Mar 20 '18 at 17:09

Anand agrawal · Answer 8 · 2019-06-15T09:36:46.537

3

UPDATE table_name 
  SET column_name=CASE 
WHEN column_name in ('value1', 'value2',.....) 
  THEN 'update_value' 
WHEN column_name in ('value1', 'value2',.....) 
  THEN 'update_value' 
END

table_name = The name of table on which you want to perform operation.

column_name = The name of Column/Field of which value you want to set.

update_value = The value you want to set of column_name

edited Jun 15 '19 at 09:36

answered Aug 19 '15 at 11:12

Anand agrawal

492
6
25

11

While this code may solve the OP's problem, a few words of explanation would be even more helpful to future readers. – Thom Aug 19 '15 at 12:03

score 3 · Answer 9 · answered Jan 15 '18 at 05:33

3

select id,phno,case gender
when 'G' then 'M'
when 'L' then 'F'
else
'No gender'
end
as gender 
from contacts

answered Jan 15 '18 at 05:33

Debendra Dash

5,334
46
38

1

Why do you not explain what is being done here? It is important to give complete answers with Explanations as some newbies might need that in order to understand how this solves the issue – Gerhard Jan 15 '18 at 06:09

score 0 · Answer 10 · answered Sep 11 '20 at 21:43

0

CASE
  WHEN ebv.db_no = 22978 OR 
       ebv.db_no = 23218 OR
       ebv.db_no = 23219
  THEN 'WECS 9500' 
  ELSE 'WECS 9520' 
END as wecs_system

answered Sep 11 '20 at 21:43

krissemicolon

129
12

score -5 · Answer 11 · answered Nov 30 '16 at 20:48

-5

Select s.stock_code,s.stock_desc,s.stock_desc_ar,
mc.category_name,s.sel_price,
case when s.allow_discount=0 then 'Non Promotional Item' else 'Prmotional 
item' end 'Promotion'
From tbl_stock s inner join tbl_stock_category c on s.stock_id=c.stock_id
inner join tbl_category mc on c.category_id=mc.category_id
where mc.category_id=2 and s.isSerialBased=0

answered Nov 30 '16 at 20:48

Mohammad Shahnawaz

11

1

This answer looks like it has nothing to do with the question. – LarsTech Nov 30 '16 at 21:14
4

Please do not post bare code, also provide an explanation of what your code is doing. – Jonathan Mee Nov 30 '16 at 21:33

OR is not supported with CASE Statement in SQL Server

11 Answers11

Linked