Case keyword can be used to provide an input expression or boolean expression to compare the value in each WHEN clause.
Case expression ca be used with 2 different way:
1. to provide input expression
CASE ..input..
WHEN ..value.. THEN ..result..
...other WHEN clauses....
ELSE ..result..
END
2. to provide boolean expression
CASE
WHEN ..boolean value.. THEN ..result..
...other WHEN clauses....
ELSE ..result..
END
CASE example:
USE pubs
GO
//to use CASE clause to decide manager types (in scenario:
7 refers General Managers
12 refers Product Managers)///
SELECT TOP 10 fname,
job_id,
Managersearch = CASE job_id
WHEN 7 THEN 'General Manager'
WHEN 12 THEN 'Product Manager'
ELSE 'not manager'
END
FROM employee;
fname job_id Managersearch
-------------------- ------ ---------------
Paolo 13 not manager
Pedro 14 not manager
Victoria 6 not manager
Helen 12 Product Manager
Lesley 7 General Manager
Francisco 4 not manager
Philip 2 not manager
Aria 10 not manager
Ann 3 not manager
Anabela 8 not manager
(10 row(s) affected)