OR is not supported with CASE Statement in SQL Server

Solution 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

Solution 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

Solution 3:

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

Solution 4:

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:

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

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

Solution 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:

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