SQL Server: CASE WHEN OR THEN ELSE END => the OR is not supported
The
OR
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
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
ASE
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
CASE WHEN ebv.db_no IN (22978, 23218, 23219) THEN 'WECS 9500'
ELSE 'WECS 9520'
END as wecs_system
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.
No comments:
Post a Comment