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 

Mischief

SQL Standard allows for multiple values: https://mcmap.net/q/65533/-case-in-statement-with-multiple-values – Forworn

Answer 1

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 

Since

For the second case, why does only 'IN' work and not '='? – Fuzee

= 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. – Semblable

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

"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? – Aegisthus

@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. – Kettle

@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. – Aegisthus

@Heriberto Lugo Which pretty much result in the same thing. :) – Kettle

@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 – Aegisthus

The closest would be shell: case $db_no in 22978|23218|23219) echo 'WECS 9500' ;; *) echo 'WECS 9520' ;; esac – Ney

@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. – Sphacelus

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

Answer 2

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 

Anarch

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

@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. – Bessel

@Bigwheels - Wow.. this was a while ago. I probably disagreed because, logically, it is exactly the same as other responses. 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 is more compact. BTW, it is not a slam on Darren's answer, which is perfectly valid. Just my $0.02 :) – Dorty

using IN keyword is much better way – Vinavinaceous

Answer 3

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

Hyacinthia

Answer 4

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

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

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

Heman

Answer 5

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:

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

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

Consonance

Answer 6

Try

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

Christner

Answer 7

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;

Olgaolguin

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

Answer 8

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

Echo

While this code may solve the OP's problem, a few words of explanation would be even more helpful to future readers. – Blodgett

Answer 9

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

Tradeswoman

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 – Prod

Answer 10

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 

Cardsharp

Answer 11

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 

Accordant

This answer looks like it has nothing to do with the question. – Tremain

Please do not post bare code, also provide an explanation of what your code is doing. – Cavein