What is the difference between a simple CASE and a searched CASE in DB2?

A simple CASE compares one expression against a list of values: CASE column WHEN 'A' THEN 'Alpha' WHEN 'B' THEN 'Beta' ELSE 'Other' END. A searched CASE evaluates each WHEN clause as a separate Boolean condition: CASE WHEN salary > 80000 THEN 'Senior' WHEN salary > 50000 THEN 'Mid' ELSE 'Junior' END. Use simple CASE for equality checks; use searched CASE for range conditions and complex comparisons.

Can DB2 CASE be used in WHERE and ORDER BY clauses?

Yes. DB2 CASE expressions can appear anywhere a value expression is allowed — in SELECT lists, WHERE conditions, ORDER BY, GROUP BY, HAVING, and UPDATE SET clauses. For example: ORDER BY CASE WHEN WORKDEPT = 'A00' THEN 1 ELSE 2 END will sort department A00 records first.

What happens if no WHEN condition matches and there is no ELSE in DB2 CASE?

If no WHEN clause matches and no ELSE clause is present, the CASE expression returns NULL. This is the SQL standard behaviour and applies to both DB2 LUW and DB2 for z/OS. It is best practice to always include an ELSE clause to make the default value explicit and to avoid unexpected NULLs in results.

Is DB2 DECODE function the same as CASE?

DECODE is not a native DB2 function — it is Oracle-specific. DB2 does not support DECODE. The equivalent in DB2 is the simple CASE expression: CASE expr WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE rd END. If you are migrating SQL from Oracle to DB2, replace all DECODE calls with simple CASE expressions.

DB2 CASE Expressions: Complete Guide with Examples

The CASE expression is one of the most versatile tools in DB2 SQL. It brings conditional logic — the kind you'd normally write in application code with if/else statements — directly into your SQL queries. This lets you classify data, transform values, control sorting, and build pivot-style reports entirely within a single SQL statement.

DB2 supports two forms: simple CASE (equality comparison) and searched CASE (arbitrary Boolean conditions). Both can appear in SELECT, WHERE, ORDER BY, GROUP BY, HAVING, and UPDATE statements.

Simple CASE Expression

A simple CASE compares a single expression against a fixed list of values:

sql

CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

Example: Expanding Abbreviations

sql

SELECT EMPNO,
       LASTNAME,
       SEX,
       CASE SEX
           WHEN 'M' THEN 'Male'
           WHEN 'F' THEN 'Female'
           ELSE          'Not specified'
       END AS GENDER_LABEL
FROM   EMPLOYEE;

Example: Job Code to Description

sql

SELECT EMPNO,
       LASTNAME,
       JOB,
       CASE JOB
           WHEN 'MANAGER'  THEN 'Department Manager'
           WHEN 'ANALYST'  THEN 'Systems Analyst'
           WHEN 'DESIGNER' THEN 'UX Designer'
           WHEN 'CLERK'    THEN 'Administrative Clerk'
           ELSE                 JOB   -- fall back to the raw code
       END AS JOB_TITLE
FROM   EMPLOYEE;

Searched CASE Expression

A searched CASE evaluates each WHEN clause as an independent Boolean condition. DB2 evaluates conditions top-to-bottom and returns the result of the first WHEN that is TRUE:

sql

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Example: Salary Bands

sql

SELECT EMPNO,
       LASTNAME,
       SALARY,
       CASE
           WHEN SALARY >= 90000 THEN 'Executive'
           WHEN SALARY >= 70000 THEN 'Senior'
           WHEN SALARY >= 50000 THEN 'Mid-Level'
           WHEN SALARY >= 30000 THEN 'Junior'
           ELSE                      'Trainee'
       END AS SALARY_BAND
FROM   EMPLOYEE
ORDER BY SALARY DESC;

Example: Tenure Classification

sql

SELECT EMPNO,
       LASTNAME,
       HIREDATE,
       CASE
           WHEN DAYS(CURRENT DATE) - DAYS(HIREDATE) >= 365 * 10 THEN 'Veteran (10+ years)'
           WHEN DAYS(CURRENT DATE) - DAYS(HIREDATE) >= 365 * 5  THEN 'Experienced (5-10 years)'
           WHEN DAYS(CURRENT DATE) - DAYS(HIREDATE) >= 365 * 2  THEN 'Developing (2-5 years)'
           ELSE                                                       'New (<2 years)'
       END AS TENURE_GROUP
FROM   EMPLOYEE;

CASE in WHERE Clauses

CASE can act as a conditional filter:

sql

-- Return senior employees in department A00, or any high earner in other depts
SELECT EMPNO, LASTNAME, WORKDEPT, SALARY
FROM   EMPLOYEE
WHERE  CASE
           WHEN WORKDEPT = 'A00' AND EDLEVEL >= 18  THEN 1
           WHEN WORKDEPT <> 'A00' AND SALARY > 80000 THEN 1
           ELSE 0
       END = 1;

While this works, it is generally clearer to rewrite as explicit OR conditions:

sql

WHERE (WORKDEPT = 'A00' AND EDLEVEL >= 18)
   OR (WORKDEPT <> 'A00' AND SALARY > 80000)

CASE in WHERE is most useful when the conditional logic cannot be expressed cleanly with AND/OR, such as when the filter depends on a computed value.

CASE in ORDER BY

Control custom sort order without adding a sort column to your result:

sql

-- Sort so department A00 comes first, then B01, then everything else alphabetically
SELECT EMPNO, LASTNAME, WORKDEPT
FROM   EMPLOYEE
ORDER BY
    CASE WORKDEPT
        WHEN 'A00' THEN 1
        WHEN 'B01' THEN 2
        ELSE            3
    END,
    WORKDEPT;

-- Sort NULLs to the bottom (without NULLS LAST syntax)
SELECT EMPNO, LASTNAME, BONUS
FROM   EMPLOYEE
ORDER BY
    CASE WHEN BONUS IS NULL THEN 1 ELSE 0 END,
    BONUS DESC;

CASE in GROUP BY — Bucket Grouping

Group rows into user-defined categories:

sql

SELECT CASE
           WHEN SALARY >= 80000 THEN 'High'
           WHEN SALARY >= 50000 THEN 'Mid'
           ELSE                      'Low'
       END                  AS SALARY_TIER,
       COUNT(*)              AS EMPLOYEE_COUNT,
       AVG(SALARY)           AS AVG_SALARY,
       MIN(SALARY)           AS MIN_SALARY,
       MAX(SALARY)           AS MAX_SALARY
FROM   EMPLOYEE
GROUP BY CASE
             WHEN SALARY >= 80000 THEN 'High'
             WHEN SALARY >= 50000 THEN 'Mid'
             ELSE                      'Low'
         END
ORDER BY AVG_SALARY DESC;

Note that the CASE expression must be repeated in GROUP BY — you cannot reference the alias SALARY_TIER in GROUP BY.

CASE in HAVING

Filter grouped results conditionally:

sql

SELECT WORKDEPT,
       COUNT(*)    AS HEADCOUNT,
       AVG(SALARY) AS AVG_SAL
FROM   EMPLOYEE
GROUP BY WORKDEPT
HAVING CASE
           WHEN COUNT(*) >= 5 AND AVG(SALARY) >= 60000 THEN 1
           WHEN COUNT(*) < 5  AND AVG(SALARY) >= 80000 THEN 1
           ELSE 0
       END = 1;

CASE in UPDATE — Conditional Column Assignment

Update different rows with different values in a single pass:

sql

-- Give department-specific raises in one UPDATE
UPDATE EMPLOYEE
SET SALARY = SALARY * CASE WORKDEPT
                          WHEN 'A00' THEN 1.10   -- 10% raise
                          WHEN 'B01' THEN 1.08   -- 8% raise
                          WHEN 'C01' THEN 1.06   -- 6% raise
                          ELSE            1.03   -- 3% raise for all others
                      END
WHERE HIREDATE < '2020-01-01';

This is far more efficient than running four separate UPDATE statements, as it requires only one table scan.

CASE with NULL Handling

CASE integrates naturally with NULL logic:

sql

-- Replace NULL bonuses with a computed default
SELECT EMPNO,
       LASTNAME,
       CASE
           WHEN BONUS IS NULL THEN SALARY * 0.02   -- 2% default
           WHEN BONUS = 0     THEN SALARY * 0.01   -- 1% minimum
           ELSE                    BONUS
       END AS EFFECTIVE_BONUS
FROM   EMPLOYEE;

Remember: CASE evaluates NULL-safe comparisons. WHEN BONUS = NULL never matches because NULL = NULL is UNKNOWN, not TRUE. Always use WHEN BONUS IS NULL.

Nested CASE Expressions

CASE expressions can be nested, though deeply nested CASE reduces readability:

sql

SELECT EMPNO,
       LASTNAME,
       CASE
           WHEN WORKDEPT = 'A00' THEN
               CASE
                   WHEN SALARY >= 80000 THEN 'Senior A00 Leader'
                   ELSE                      'A00 Staff'
               END
           WHEN WORKDEPT = 'B01' THEN
               CASE
                   WHEN JOB = 'MANAGER' THEN 'B01 Manager'
                   ELSE                      'B01 Contributor'
               END
           ELSE 'Other Department'
       END AS CLASSIFICATION
FROM   EMPLOYEE;

When nesting gets deep, consider using a lookup table and JOIN instead — it is often more maintainable.

CASE for Pivot-Style Reports

One of the most powerful uses of CASE is creating pivot tables — transforming row values into columns:

sql

-- Count employees per job per department in a cross-tab layout
SELECT WORKDEPT,
       SUM(CASE WHEN JOB = 'MANAGER'  THEN 1 ELSE 0 END) AS MANAGERS,
       SUM(CASE WHEN JOB = 'ANALYST'  THEN 1 ELSE 0 END) AS ANALYSTS,
       SUM(CASE WHEN JOB = 'DESIGNER' THEN 1 ELSE 0 END) AS DESIGNERS,
       SUM(CASE WHEN JOB = 'CLERK'    THEN 1 ELSE 0 END) AS CLERKS,
       COUNT(*)                                           AS TOTAL
FROM   EMPLOYEE
GROUP BY WORKDEPT
ORDER BY WORKDEPT;

This produces one row per department with separate columns for each job type — a classic pivot transformation.

CASE vs DECODE vs IIF

Function	DB2 LUW	DB2 z/OS	Notes
CASE WHEN	✅	✅	Standard SQL, always use this
DECODE	❌	❌	Oracle-only, not in DB2
IIF	❌	❌	SQL Server-only, not in DB2
COALESCE	✅	✅	Shortcut for NULL replacement
NULLIF	✅	✅	Converts value to NULL

When migrating from Oracle, replace every DECODE with simple CASE. When migrating from SQL Server, replace IIF with searched CASE WHEN.

Performance Considerations

CASE expressions are evaluated row by row, which is generally efficient. A few tips:

Put the most frequently matched WHEN clause first — DB2 short-circuits as soon as a matching WHEN is found.

Avoid calling expensive functions inside CASE when possible. For example, instead of CASE WHEN UPPER(lastname) = 'SMITH' repeatedly, normalise the data once in a CTE.

For very large tables where you're grouping by a CASE expression, consider adding a generated column that stores the classification and indexing it.

Complete Example: Employee Scorecard

sql

SELECT E.WORKDEPT,
       D.DEPTNAME,
       E.EMPNO,
       E.FIRSTNME || ' ' || E.LASTNAME AS EMPLOYEE,
       E.SALARY,
       CASE
           WHEN E.SALARY >= 90000 THEN '⭐⭐⭐ Principal'
           WHEN E.SALARY >= 70000 THEN '⭐⭐ Senior'
           WHEN E.SALARY >= 50000 THEN '⭐ Mid'
           ELSE                        'Junior'
       END AS GRADE,
       CASE E.SEX WHEN 'M' THEN 'Male' WHEN 'F' THEN 'Female' ELSE 'N/A' END AS GENDER,
       CASE
           WHEN E.BONUS IS NULL OR E.BONUS = 0 THEN 'No bonus'
           WHEN E.BONUS >= 5000               THEN 'High bonus'
           ELSE                                    'Standard bonus'
       END AS BONUS_STATUS,
       CASE
           WHEN DAYS(CURRENT DATE) - DAYS(E.HIREDATE) >= 365 * 10 THEN 'Veteran'
           WHEN DAYS(CURRENT DATE) - DAYS(E.HIREDATE) >= 365 * 5  THEN 'Experienced'
           ELSE                                                          'Growing'
       END AS TENURE
FROM   EMPLOYEE E
JOIN   DEPARTMENT D ON E.WORKDEPT = D.DEPTNO
ORDER BY E.WORKDEPT, E.SALARY DESC;

Next Steps

CASE expressions are foundational to advanced DB2 SQL. You will use them constantly when writing window functions, stored procedures, and triggers. To see CASE in action within DB2 for z/OS environments, see the DB2 for z/OS Introduction. For the full learning path, visit the DB2 Mastery course hub.

DB2 CASE Expressions: Searched, Simple, and Nested Examples