CASE expressions

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

END

Ends a case-expression.

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 DSN8A10.EMP) X
         GROUP BY CASE_DEPT;