CASE expressions

A CASE expression allows an expression to be selected based on the evaluation of one or more conditions.

Read syntax diagramSkip visual syntax diagramCASEsearched-when-clausesimple-when-clauseELSE NULLELSEresult-expressionEND

searched-when-clause:

Read syntax diagramSkip visual syntax diagramWHENsearch-conditionTHENresult-expressionNULL

simple-when-clause:

Read syntax diagramSkip visual syntax diagramexpressionWHENexpressionTHENresult-expressionNULL
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 case evaluates to true and the ELSE keyword is present, the result is the value of the result-expression or NULL. If no case evaluates to true and the ELSE keyword is not present, the result is NULL. When a case evaluates to unknown (because of NULL values), the case is NOT true and hence is treated the same way as a case that evaluates to false.
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
  • Start of changeThe diagnostic-string-expression of a SIGNAL statementEnd of change
  • Start of changeAn ORDER BY clause or a GROUP BY clauseEnd of change
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.

Table 1. Equivalent case expressions
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)
Example 1 (simple-when-clause): Assume that in the EMPLOYEE table the first character of a department number represents the division in the organization. Use a CASE expression to list the full name of the division to which each employee belongs.
   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;
Example 2 (searched-when-clause): You can also use a CASE expression to avoid division by zero errors. From the EMPLOYEE table, find all employees who earn more than 25 percent of their income from commission, but who are not fully paid on commission:
   SELECT EMPNO, WORKDEPT, SALARY+COMM FROM EMPLOYEE
   WHERE (CASE WHEN SALARY=0 THEN 0
               ELSE COMM/(SALARY+COMM)
               END) > 0.25;
Example 3 (searched-when-clause): You can use a CASE expression to avoid division by zero errors in another way. The following queries show an accumulation or summing operation. In the first query, Db2 performs the division before performing the CASE statement and an error occurs along with the results.
   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;
Example 4: This example shows how to group the results of a query by a CASE expression without having to re-type the expression. Using the sample employee table, find the maximum, minimum, and average salary. Instead of finding these values for each department, assume that you want to combine some departments into the same group.
   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 DSN8B10.EMP) X
         GROUP BY CASE_DEPT;