2.17 CASE expressions

Organization of this section
(1) Function
(2) Format
(3) Rules
(4) Examples

(1) Function

A CASE expression specifies a value with a condition.

(2) Format

CASE-expression::={searched-CASE-expression|simple-CASE-expression|
                     CASE-abbreviation}
searched-CASE-expression::=CASE
                        {WHEN search-condition THEN {value-expression
                             |NULL}}...
                        [ELSE {value-expression|NULL}]
                       END
simple-CASE-expression::=CASE value-expression
                        {WHEN value-condition THEN {value-expression
                             |NULL}}...
                        [ELSE {value-expression|NULL}]...
                       END
CASE-abbreviation::={NULLIF (value-expression, value-expression)
                  |COALESCE (value-expression, [, value-expression]...)}

(3) Rules

  1. A maximum of 255 WHEN statements can be specified in a single CASE expression.
  2. If some of the search conditions in the CASE expression are TRUE, the result value of the first WHEN for which the search condition is TRUE is converted to the CASE expression data type and used as the value of the CASE expression.
  3. If none of the search conditions in the CASE expression is TRUE, the result value of the ELSE that is assumed or specified is converted to the CASE expression data type and used as the value of the CASE expression.
  4. For search conditions, see 2.7 Search conditions.
  5. The data type and data length of the result of a CASE expression are the same as for the set operation.
  6. The data type of the result of a CASE expression is without the NOT NULL constraint (the null value is allowed).
  7. In the case of COALESCE, value expressions are evaluated from left to right and the first non-null value is used as the result.
  8. A value expression must be specified in at least one THEN in a CASE expression.
  9. The ? parameter or an embedded variable cannot be specified alone in the value expression in CASE, THEN, or ELSE (including specification in monomial operational expressions).
  10. If ELSE is not specified, ELSE NULL is assumed.
  11. Values with any of the following data types cannot be specified in a CASE value expression, WHEN value expression, or NULLIF and COALESCE value expressions:
    • BLOB
    • BINARY with a minimum definition length of 32,001 bytes
    • BOOLEAN
    • Abstract data type
  12. Values with the following data type cannot be specified in THEN or ELSE value expressions:
    • BLOB
    • BINARY with a minimum definition length of 32,001 bytes
    • BOOLEAN
    • Abstract data type
  13. A simple CASE expression is the same as a searched CASE expression for which V2=V1 is specified as a search condition, where V1 is a value expression in WHEN and V2 is a value expression in CASE.
  14. The ? parameter or an embedded variable cannot be specified alone in the value expression in the first WHEN in a simple CASE specification, the first value expression in COALESCE, or both value expressions in NULLIF (including specification in monomial operational expressions).
  15. If the value expression in at least one WHEN in a simple CASE expression is the ? parameter or an embedded variable, the data type of the ? parameter or embedded variable is assumed to be the same as that of the value expression in the first WHEN.
  16. If one of the value expressions in NULLIF is the ? parameter or an embedded variable, the data type of the ? parameter or embedded variable is assumed to be the same as that of the other value expression.
  17. If at least one value expression in COALESCE is the ? parameter or an embedded variable, the data type of the ? parameter or embedded variable is assumed to be the same as that of the first value expression.
  18. A maximum of 255 value expressions can be specified in COALESCE.
  19. NULLIF(V1, V2) is the same as the following CASE expression:

    CASE WHEN V1=V2 THEN NULL ELSE V1 END

  20. COALESCE(V1, V2) is the same as the following scalar function VALUE or CASE expression:

    VALUE(V1, V2)
    CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END

  21. COALESCE(V1, V2, ..., Vn), where n is at least 3, is the same as the following scalar function VALUE or CASE expression:

    VALUE(V1, V2, ..., Vn)
    CASE WHEN V1 IS NOT NULL THEN V1 ELSE COALESCE (V2, ..., Vn) END

  22. Comparable data types should be used for the THEN and ELSE value expressions in a searched CASE expression or a simple CASE expression. For the comparable data types, see 1.2 Data types. The following data types cannot be compared:
    • Date data and its character string representation
    • Time data and its character string representation
    • Time stamp data and the character string representation of time stamp data
    • Date interval data and its decimal representation
    • Time interval data and its decimal representation
    • Binary data and hexadecimal character string literals
  23. In the case of a simple CASE expression, comparable data types should be used for the value expressions of WHEN and CASE. For the comparable data types, see 1.2 Data types. The following data types cannot be compared:
    • Date data and its character string representation
    • Time data and its character string representation
    • Time stamp data and the character string representation of time stamp data
    • Date interval data and its decimal representation
    • Time interval data and its decimal representation
    • Binary data and hexadecimal character string literals
  24. In the case of NULLIF or COALESCE, comparable data types should be used for their value expressions. For the comparable data types, see 1.2 Data types. The following data types cannot be compared:
    • Date data and its character string representation
    • Time data and its character string representation
    • Date interval data and its decimal representation
    • Time interval data and its decimal representation
  25. By specifying a subscript, repetition columns can be specified in a CASE expression. Also, ANY as a subscript can be specified for a repetition column as a search condition in a CASE expression. Unsubscripted repetition columns can be specified in the IS NULL predicate (at the same locations as in a search condition). ANY as a subscript cannot be specified for a repetition column in a CASE expression that is specified in a selection expression.
  26. A window function cannot be specified.

(4) Examples

(a) Searched CASE

In table T1, change "AA" and "BB" in column C1 to "AAA" and "BBB", respectively:

UPDATE T1 SET C1 =
  CASE WHEN C1='AA' THEN 'AAA' WHEN C1='BB' THEN 'BBB'
  ELSE C1
END

[Figure]

(b) Simple CASE

In table T1, change "AA" and "BB" in column C1 to "AAA" and "BBB", respectively:

UPDATE T1 SET C1 =
  CASE C1 WHEN 'AA' THEN 'AAA' WHEN 'BB' THEN 'BBB'
  ELSE C1
END

[Figure]

(c) CASE abbreviation (COALESCE)

Extract columns that do not contain all null values from table T1, in the order of columns C1, C2, and C3; if all columns contain only null values, set 0 as the result:

SELECT COALESCE(C1,C2,C3,0) FROM T1

[Figure]

* Null value

(d) CASE abbreviation (NULLIF)

If the values in columns C1 and C2 of table T1 are equal, the null value is returned as the result; if the values in columns C1 and C2 of table T1 are not equal, column C1 is extracted:

SELECT NULLIF(C1,C2) FROM T1

[Figure]

* Null value