CASE expressions
A CASE expression allows an expression to be selected based on the evaluation of one or more conditions.
>>-CASE--+-searched-when-clause-+-------------------------------> '-simple-when-clause---' .-ELSE NULL---------------. >--+-------------------------+--END---------------------------->< '-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--------------'
- searched-when-clause
- Specifies
a search-condition that is applied to each
row or group of table data presented for evaluation, and the result
when that condition is true.
Pair-wise comparison is performed. Implicit cast of each pair follows the same rule as for a basic predicate. The searched-when-clause performs implicit cast on string and numeric search conditions.
- simple-when-clause
- Specifies that the value of the expression prior
to the first WHEN keyword is tested for equality with the value of
each expression that follows the WHEN keyword.
It also specifies the result for when that condition is true.
The data type of the expression prior to the first WHEN keyword must be compatible with the data types of the expression that follows each WHEN keyword. The data type of any of the expressions cannot be a CLOB, DBCLOB or BLOB. In addition, the expression prior to the first WHEN keyword cannot include a function that is not deterministic or has an external action.
- result-expression or NULL
- Specifies the value that follows the THEN and ELSE keywords. It
specifies the result of a searched-when-clause or
a simple-when-clause that is true, or the
result if no case is true. There must be at least one result-expression in
the CASE expression with a defined data type. NULL cannot be specified
for every case.
All result-expressions must have compatible data types. The attributes of the result are determined according to the rules that are described in Rules for result data types. When the result is a string, its attributes include a CCSID. For the rules on how the CCSID is determined, see Determining the encoding scheme and CCSID of a string.
- search-condition
- Specifies a condition that is true, false, or unknown
about a row or group of table data. The search-condition can
be a predicate, including predicates that contain fullselects (scalar
or non-scalar) or row-value expressions. If search-condition in a searched-when-clause specifies a quantified predicate or an IN predicate that includes a fullselect, the CASE expression cannot be used in the following contexts:
- select lists
- a VALUES clause of an INSERT or MERGE statement
- a SET or assignment clause of an UPDATE, MERGE, or DELETE statement
- the right side of a SET or assignment statement
- the definition of a column mask or a row permission
If search-condition in a searched-when-clause specifies an EXISTS predicate, the CASE expression cannot be used in the following contexts:- a VALUES clause of an INSERT or MERGE statement
- the right side of a SET or assignment statement
- END
- Ends a case-expression.
If a CASE expression is in a select list that derives the final result table, and if the simple-when-clause or the searched-when-clause references a basic predicate with a fullselect, column masks cannot be applied to the columns in the THEN clauses which derive the result of the CASE expression.
If a CASE expression is in a select list that derives the final result table, and if the simple-when-clause or searched-when-clause references a column for which column access control is activated, the column mask cannot be applied to the column and an error is returned.
If a CASE expression is in a SET clause of an UPDATE, MERGE, or DELETE statement, a VALUES clause of an INSERT or MERGE statement, or the fullselect of an INSERT from a fullselect, and if the simple-when-clause or the searched-when-clause references a column for which column access control is activated, the column access control is ignored for the column.
Two scalar functions, NULLIF and COALESCE, 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 'Design'
WHEN 'D' THEN 'Operations'
END
FROM EMPLOYEE;
SELECT EMPNO, WORKDEPT, SALARY+COMM FROM EMPLOYEE
WHERE (CASE WHEN SALARY=0 THEN 0
ELSE COMM/(SALARY+COMM)
END) > 0.25;
SELECT REF_ID,PAYMT_PAST_DUE_CT,
CASE
WHEN PAYMT_PAST_DUE_CT=0 THEN 0
WHEN PAYMT_PAST_DUE_CT>0 THEN
SUM(BAL_AMT/PAYMT_PAST_DUE_CT)
END
FROM PAY_TABLE
GROUP BY REF_ID,PAYMT_PAST_DUE_CT;
However, if the CASE
expression is included in the SUM aggregate function, the CASE expression
would prevent the errors. In the following query, the CASE expression
screens out the unwanted division because the CASE operation is performed
before the division. SELECT REF_ID,PAYMT_PAST_DUE_CT,
SUM(CASE
WHEN PAYMT_PAST_DUE_CT=0 THEN 0
WHEN PAYMT_PAST_DUE_CT>0 THEN
BAL_AMT/PAYMT_PAST_DUE_CT
END)
FROM PAY_TABLE
GROUP BY REF_ID,PAYMT_PAST_DUE_CT;
SELECT CASE_DEPT,MAX(SALARY),MIN(SALARY),AVG(SALARY)
FROM (SELECT SALARY,CASE WHEN WORKDEPT = 'A00' OR WORKDEPT = 'E21'
THEN 'A00_E21'
WHEN WORKDEPT = 'D11' OR WORKDEPT = 'E11'
THEN 'D11_E11'
ELSE WORKDEPT
END AS CASE_DEPT
FROM DSN8A10.EMP) X
GROUP BY CASE_DEPT;