DECODE scalar function

The DECODE function compares each expression2 to expression1. If expression1 is equal to expression2, or both expression1 and expression2 are null, the value of the result-expression is returned. If no expression2 matches expression1, the value of else-expression is returned. Otherwise a null value is returned.

Read syntax diagramSkip visual syntax diagramDECODE(expression1 ,expression2,result-expression ,else-expression )

The schema is SYSIBM.

The DECODE function is similar to the CASE expression, with the exception of how DECODE handles null values:

  • A null value in expression1 will match a corresponding null value in expression2.
  • If the NULL keyword is used as an argument in the DECODE function, it must be cast to a data type that is appropriate for comparison.

An argument of DECODE must not represent an array value.

The rules for determining the result type of the result of the DECODE function are based on the corresponding CASE expression.

The following table shows equivalent DECODE functions and CASE expressions. Both the DECODE function and the corresponding CASE expression achieve the same result.

Table 1. Equivalent DECODE functions and CASE expressions (each returns the same results)
DECODE function CASE expression Notes 
DECODE(c1, 7, 'a', 
  6, 'b', 'c')
 
CASE c1
  WHEN 7 THEN 'a'
  WHEN 6 THEN 'b'
  ELSE 'c'
END
   
DECODE(c1, var1, 'a', 
  var2, 'b')
 
CASE
WHEN c1 = var1 OR 
	(c1 IS NULL AND 
    var1 IS NULL) THEN 'a'
WHEN c1 = var2 OR 
	(c1 IS NULL AND 
    var2 IS NULL) THEN 'b'
ELSE NULL
END
 The values of c1, var1, and var2 can be null values. 
SELECT ID, DECODE(STATUS, 
     'A', 'Accepted',
      D', 'Denied',
CAST(NULL AS VARCHAR(1)), 
   'Unknown', 'Other')
FROM CONTRACTS
 
SELECT ID,
CASE
WHEN STATUS = 'A' 
  THEN 'Accepted'
WHEN STATUS = 'D' 
  THEN 'Denied'
WHEN STATUS IS NULL 
  THEN 'Unknown'
ELSE 'Other'
END
FROM CONTRACTS