CASE and searched expressions in DB2

Two forms of CASE

Evaluation rule for both: the result is the THEN (or ELSE) of the first leftmost WHEN that is true. Later WHENs are not considered. Unknown is not true. If nothing is true: ELSE result, or NULL if ELSE is omitted.

All result-expressions must have compatible types (SQLSTATE 42804). At least one THEN/ELSE must be a non-null result-expression—you cannot write CASE with only NULL results (SQLSTATE 42625).

Simple CASE

Simple CASE tests the expression after CASE for equality with each WHEN expression. Types must be comparable. The expression before the first WHEN must not be a non-deterministic or external-action function (SQLSTATE 42845).

1
2
3
4
5
6
7
8
SELECT EMPNO, LASTNAME,
       CASE WORKDEPT
         WHEN 'A00' THEN 'SPIFFY COMPUTER SERVICE DIV.'
         WHEN 'B01' THEN 'PLANNING'
         WHEN 'C01' THEN 'INFORMATION CENTER'
         ELSE 'OTHER / UNKNOWN'
       END AS DEPT_NAME
  FROM DSN8C10.EMP;

Think of it as a switch on one value. You cannot write WHEN SALARY > 50000 in a simple CASE—that needs searched CASE. You also cannot match nulls: CASE WORKDEPT WHEN NULL THEN ... never fires, because null = null is unknown.

Searched CASE

Searched CASE has no expression between CASE and the first WHEN. Each WHEN is a search-condition (a predicate, possibly AND/OR).

1
2
3
4
5
6
7
8
9
10
SELECT EMPNO, FIRSTNME, LASTNAME,
       CASE
         WHEN EDLEVEL <= 12 THEN 'HIGH SCHOOL OR LESS'
         WHEN EDLEVEL > 12 AND EDLEVEL <= 14 THEN 'JUNIOR COLLEGE'
         WHEN EDLEVEL > 14 AND EDLEVEL <= 17 THEN 'FOUR-YEAR COLLEGE'
         WHEN EDLEVEL > 17 THEN 'GRADUATE SCHOOL'
         ELSE 'UNKNOWN'
       END AS EDUCATION
  FROM DSN8C10.EMP
 WHERE JOB = 'FLD';

IBM uses this pattern to decode codes and to avoid division by zero:

1
2
3
4
5
6
SELECT EMPNO, WORKDEPT,
       CASE
         WHEN SALARY = 0 THEN NULL
         ELSE COMM / SALARY
       END AS COMMISSION_RATIO
  FROM DSN8C10.EMP;

Order WHEN clauses from specific to general. Overlapping ranges are legal; the first true wins, so put the tighter test first.

Nested CASE

A THEN or ELSE can itself be a CASE. Nesting is useful for two-dimensional rules (job and department) without a huge AND mesh. Keep it readable—three levels is already a maintenance hazard.

1
2
3
4
5
6
7
8
9
CASE
  WHEN JOB = 'MANAGER' THEN
    CASE WORKDEPT
      WHEN 'A00' THEN 'EXEC MGR'
      ELSE 'DEPT MGR'
    END
  WHEN JOB = 'SALESREP' THEN 'SALES'
  ELSE 'STAFF'
END

SQL PL CASE statements (control flow in procedures) have a nesting limit of three for simple WHEN and no documented nesting limit for searched WHEN. That is the statement, not the expression. Expressions nest as the SQL Reference allows; prefer flattening over deep nests.

CASE NULL behaviour

• Unknown WHEN is skipped — same as false
• Simple CASE never matches null — use WHEN col IS NULL in searched CASE
• Omitted ELSE → null result
• ELSE NULL is explicit and clearer in reports
• NULLIF(e1, e2) — null if e1 = e2, else e1 (unknown equality yields e1, not null)
• COALESCE(e1, e2, ...) — first non-null argument

1
2
3
4
5
6
7
8
9
-- Null-safe label
CASE
  WHEN COMM IS NULL THEN 'NO COMMISSION'
  WHEN COMM = 0 THEN 'ZERO'
  ELSE 'HAS COMMISSION'
END
 
-- First non-null phone
COALESCE(WORKPHONE, HOMEPHONE, 'NONE')

If EDLEVEL is null in the education CASE above, every comparison is unknown, ELSE runs, and IBM’s example shows UNKNOWN. That is why ELSE is not optional if you need a defined label for nulls.

CASE in SELECT

The SELECT list is the most common home. Alias the result. Compatible types across THEN/ELSE matter: mixing CHAR and INTEGER forces a conversion or an error—cast explicitly (CHAR, DECIMAL) so reports stay predictable.

CASE in WHERE

Legal, but usually a stage 2 predicate. Prefer:

1
2
3
4
5
-- Residual (CASE in WHERE)
WHERE CASE WHEN SALARY > 100000 THEN 'Y' ELSE 'N' END = 'Y'
 
-- Index-friendlier equivalent
WHERE SALARY > 100000

Use CASE in WHERE only when the logic cannot be expressed as ordinary predicates (rare) or when the query is already filtered tightly by other indexable predicates.

CASE in ORDER BY

Custom sort sequences that are not the column’s collating sequence:

1
2
3
4
5
6
7
8
9
10
SELECT EMPNO, JOB, LASTNAME
  FROM DSN8C10.EMP
 ORDER BY
       CASE JOB
         WHEN 'PRES' THEN 1
         WHEN 'MANAGER' THEN 2
         WHEN 'SALESREP' THEN 3
         ELSE 4
       END,
       LASTNAME;

Searched CASE in ORDER BY cannot use EXISTS, quantified predicates, or IN with a fullselect. Keep the WHEN conditions simple comparisons.

CASE in GROUP BY

Bucket rows without a helper column. You must repeat the CASE in GROUP BY; the SELECT alias is not a grouping-expression.

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT CASE
         WHEN SALARY < 50000 THEN 'LOW'
         WHEN SALARY < 80000 THEN 'MID'
         ELSE 'HIGH'
       END AS SAL_BAND,
       COUNT(*) AS N,
       AVG(SALARY) AS AVG_SAL
  FROM DSN8C10.EMP
 GROUP BY CASE
            WHEN SALARY < 50000 THEN 'LOW'
            WHEN SALARY < 80000 THEN 'MID'
            ELSE 'HIGH'
          END;

Same restriction as ORDER BY: no EXISTS / quantified / IN-fullselect in the searched WHEN of a GROUP BY CASE. A nested table expression is the escape hatch: compute CASE in an inner SELECT, group on the named column outside.

CASE in UPDATE

SET a column from a CASE so one statement applies different formulas:

1
2
3
4
5
6
7
8
UPDATE DSN8C10.EMP
   SET SALARY = SALARY * CASE WORKDEPT
                           WHEN 'A00' THEN 1.10
                           WHEN 'B01' THEN 1.08
                           WHEN 'C01' THEN 1.06
                           ELSE 1.03
                         END
 WHERE HIREDATE < '2010-01-01';

Searched CASE works the same in SET. Combine with WHERE so you do not multiply rows you did not intend to touch. Check compatible numeric types so DECIMAL salaries do not unexpectedly become floating-point.

CASE with aggregates

Conditional aggregation (pivot-style) uses CASE inside SUM/COUNT/AVG:

1
2
3
4
5
SELECT SUM(SALARY) AS TOT_SAL,
       SUM(CASE WHEN WORKDEPT = 'A00' THEN SALARY END) AS A00_SAL,
       SUM(CASE WHEN WORKDEPT = 'B01' THEN SALARY END) AS B01_SAL,
       COUNT(CASE WHEN JOB = 'MANAGER' THEN 1 END) AS MGR_N
  FROM DSN8C10.EMP;

THEN SALARY with omitted ELSE yields null for other departments; SUM ignores nulls, so you get the department total. COUNT(CASE WHEN ... THEN 1 END) counts matches; COUNT(CASE WHEN ... THEN 1 ELSE 0 END) is wrong because 0 is not null—COUNT would count every row. Use SUM(CASE WHEN ... THEN 1 ELSE 0 END) or COUNT of a non-null THEN without ELSE 0.

Explain It Like I'm Five

CASE is a stack of labeled boxes. Simple CASE looks at one toy and asks “is it a red car? a blue car?” Searched CASE asks any yes/no question: “is it heavier than five bricks?” The first box that says yes wins the prize inside. If every box says “I don’t know” (null) or “no,” and you did not put a last box labeled ELSE, the prize is empty (null). Putting CASE in WHERE is like making the librarian open every book to run the box game instead of using the catalog.

Exercises

1. Write a simple CASE that maps JOB values PRES, MANAGER, and anything else to ranks 1, 2, 3.
2. Write a searched CASE that returns HIGH / MID / LOW from SALARY using two thresholds of your choice, with ELSE for null salary.
3. Why does CASE WORKDEPT WHEN NULL THEN 'NONE' never return NONE?
4. Write GROUP BY salary bands and explain why you cannot GROUP BY the SELECT alias.
5. Write SUM(CASE ...) that totals SALARY for managers only, and COUNT of those managers, without counting non-managers.