Conditional functions in DB2: COALESCE, NULLIF, DECODE and CASE

CASE is the general tool

Every function on this page is a convenience spelling of CASE. When the logic has ranges, multiple columns, or LIKE predicates, write searched CASE. Use the functions when the pattern is exactly “first non-null,” “null if equal,” or “lookup this code.”

1
2
3
4
5
6
-- Searched CASE
CASE
  WHEN BONUS IS NULL THEN 0
  WHEN BONUS < 0 THEN 0
  ELSE BONUS
END

Simple CASE compares one expression to a list of values. It does not treat two nulls as equal (unknown is not true). That difference is why DECODE exists for Oracle ports.

COALESCE and VALUE

COALESCE returns the first non-null argument. Schema SYSIBM.

1
COALESCE(expression, expression [, expression ...])

• Arguments are considered in the order written.
• The result is null only if all arguments are null.
• The result type is resolved pairwise with the rules for result data types (same family of rules as UNION).
• Mixed character/graphic strings and numbers: the string is implicitly cast to DECFLOAT(34).
• VALUE is a synonym for COALESCE.

COALESCE(e1, e2) is the same as:

1
CASE WHEN e1 IS NOT NULL THEN e1 ELSE e2 END

1
2
3
4
5
6
7
8
9
10
-- Missing salary displays as 0
SELECT EMPNO, COALESCE(SALARY, 0) AS SALARY
  FROM DSN8C10.EMP;
 
-- Full outer join key that might be on either side
SELECT COALESCE(S1993.DEPTNO, S1994.DEPTNO) AS DEPT,
       S1993.SALES,
       S1994.SALES
  FROM S1993 FULL JOIN S1994
    ON S1993.DEPTNO = S1994.DEPTNO;

For names:

1
2
3
4
5
SELECT RTRIM(FIRSTNME)
       CONCAT ' '
       CONCAT COALESCE(RTRIM(MIDINIT) CONCAT ' ', '')
       CONCAT RTRIM(LASTNAME) AS FULLNAME
  FROM DSN8C10.EMP;

IFNULL and NVL

1
2
SELECT EMPNO, IFNULL(SALARY, 0)
  FROM DSN8C10.EMP;

Prefer COALESCE in new SQL even for two arguments. IFNULL and NVL show up in ported code and in people who learned another DBMS first.

NULLIF

NULLIF returns null if the two arguments are equal; otherwise it returns the first argument. Arguments must be compatible. Neither can be BLOB, CLOB, DBCLOB, or XML. Distinct types are allowed when compatible.

1
NULLIF(expression1, expression2)

Equivalent CASE:

1
CASE WHEN e1 = e2 THEN NULL ELSE e1 END

When e1 = e2 is unknown because one or both arguments is null, the CASE is not true, so NULLIF returns e1. That is easy to misread: NULLIF(NULL, 0) is null because e1 is null, not because of a match. NULLIF(0, NULL) is 0.

1
2
3
4
5
6
-- Sentinel 0 means "no bonus" for AVG
SELECT AVG(NULLIF(BONUS, 0)) AS AVG_REAL_BONUS
  FROM DSN8C10.EMP;
 
-- IBM example: 4500 + 500 equals 5000, so the result is null
VALUES NULLIF(4500.00 + 500.00, 5000.00);

DECODE

DECODE compares expression1 to each expression2. On match (including null-to-null) it returns the corresponding result-expression. If nothing matches, it returns else-expression, or null if you omit ELSE.

1
2
3
4
DECODE(expression1,
       expression2, result-expression
       [, expression2, result-expression ...]
       [, else-expression])

Differences from simple CASE:

• A null expression1 matches a null expression2.
• If you write the NULL keyword as an argument, cast it to a comparable type.
• Arguments must not be arrays.
• Result type rules follow the equivalent CASE expression.

1
2
3
4
5
6
7
SELECT ID,
       DECODE(STATUS,
              'A', 'Accepted',
              'D', 'Denied',
              CAST(NULL AS CHAR(1)), 'Unknown',
              'Other') AS STATUS_TEXT
  FROM CONTRACTS;

Equivalent searched CASE for the same null-aware map:

1
2
3
4
5
6
CASE
  WHEN STATUS = 'A' THEN 'Accepted'
  WHEN STATUS = 'D' THEN 'Denied'
  WHEN STATUS IS NULL THEN 'Unknown'
  ELSE 'Other'
END

Shop standard on z/OS: write the CASE form in new programs. Keep DECODE when you are copying Oracle SQL and need identical null matching without rewriting every branch.

Choosing among them

• Default a null to a value — COALESCE(col, 0) or COALESCE(col, '').
• Turn a sentinel into null — NULLIF(col, 0) or NULLIF(col, ' ').
• Map codes to labels, including null codes — searched CASE, or DECODE if you must.
• Ranges, LIKE, multiple columns — searched CASE only.

Combining COALESCE and NULLIF is a common “clean the column” pattern: COALESCE(NULLIF( RTRIM(col), ''), 'N/A') turns blanks and empty strings into N/A and leaves real text alone.

COBOL host variables

Indicator variables still matter. COALESCE(SALARY, 0) in SQL means the host PIC S9(7)V99 COMP-3 can be NOT NULL from the program’s point of view — you get 0 instead of a −1 indicator. That is often what you want for reports. For “was it really missing?” keep the indicator and skip COALESCE.

1
2
3
4
5
6
           EXEC SQL
                SELECT EMPNO, COALESCE(SALARY, 0)
                  INTO :EMPNO, :SALARY
                  FROM DSN8C10.EMP
                 WHERE EMPNO = :EMPNO
           END-EXEC.

Explain It Like I'm Five

COALESCE is a teacher asking kids in a line for a pencil until someone actually has one. VALUE, IFNULL, and NVL are nicknames for that teacher. NULLIF is a rule: “if these two answers are the same, pretend there is no answer.” DECODE is a sticker chart: this code gets this sticker, that code gets that sticker, and a missing code can still match a missing box on the chart. CASE is the full instruction sheet when the chart is not enough.

Exercises

1. Write COALESCE to display COMM as 0 when it is null on DSN8C10.EMP.
2. Show NULLIF so bonus zeros are excluded from AVG.
3. Rewrite IFNULL(SALARY, 0) as CASE and as COALESCE.
4. Write DECODE and an equivalent searched CASE that maps 'A'/'D'/NULL status codes.
5. Predict NULLIF(0, NULL) and NULLIF(NULL, 0).