.-ELSE NULL---------------. >>-CASE--+-searched-when-clause-+--+-------------------------+--END->< '-simple-when-clause---' '-ELSE--result-expression-' searched-when-clause: .-----------------------------------------------------. V | |----WHEN--search-condition--THEN--+-result-expression-+-+------| '-NULL--------------' simple-when-clause: |--expression---------------------------------------------------> .-----------------------------------------------. V | >----WHEN--expression--THEN--+-result-expression-+-+------------| '-NULL--------------'
CASE expressions allow an expression to be selected based on the evaluation of one or more conditions. In general, the value of the case-expression is the value of the result-expression following the first (leftmost) when-clause that evaluates to true. If no when-clause evaluates to true and the ELSE keyword is present then the result is the value of the ELSE result-expression or NULL. If no when-clause evaluates to true and the ELSE keyword is not present then the result is NULL. Note that when a when-clause evaluates to unknown (because of nulls), the when-clause is not true and hence is treated the same way as a when-clause that evaluates to false.
The data type of the expression prior to the first WHEN keyword:
All result-expressions must have compatible data types, where the attributes of the result are determined based on the Rules for result data types.
The search-condition must not include a subquery in an EXISTS or IN predicate.
There are two scalar functions, NULLIF and COALESCE, that are specialized to handle a subset of the functionality provided by CASE. The following table shows the equivalent expressions using CASE or these functions.
CASE Expression | Equivalent Expression |
---|---|
CASE WHEN e1=e2 THEN NULL ELSE e1 END | NULLIF(e1,e2) |
CASE WHEN e1 IS NOT NULL THEN e1 ELSE e2 END | COALESCE(e1,e2) |
CASE WHEN e1 IS NOT NULL THEN e1 ELSE COALESCE(e2,...,eN) END | COALESCE(e1,e2,...,eN) |
SELECT EMPNO, LASTNAME, CASE SUBSTR(WORKDEPT,1,1) WHEN 'A' THEN 'Administration' WHEN 'B' THEN 'Human Resources' WHEN 'C' THEN 'Accounting' WHEN 'D' THEN 'Design' WHEN 'E' THEN 'Operations' END FROM EMPLOYEE
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, CASE WHEN EDLEVEL < 15 THEN 'SECONDARY' WHEN EDLEVEL < 19 THEN 'COLLEGE' ELSE 'POST GRADUATE' END FROM EMPLOYEE
SELECT EMPNO, WORKDEPT, SALARY+COMM FROM EMPLOYEE WHERE (CASE WHEN SALARY=0 THEN NULL ELSE COMM/SALARY END) > 0.25
SELECT LASTNAME, CASE WHEN LASTNAME = 'Haas' THEN 'President' ... ELSE 'Unknown' END FROM EMPLOYEE SELECT LASTNAME, CASE LASTNAME WHEN 'Haas' THEN 'President' ... ELSE 'Unknown' END FROM EMPLOYEE
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.