7.29.1 Specification format and rules for CASE expressions

(1) Specification format

CASE-expression ::=
    CASE
     {WHEN search-condition THEN {value-expression|NULL}}...
     [ELSE {value-expression|NULL}]
    END

To Page Top

(2) Explanation of specification format

WHEN search-condition:

Specifies search conditions. For details about search conditions, see 7.18 Search conditions. If the specified search condition is TRUE, the value specified after the THEN is returned as the result.

Note that a maximum of 255 WHEN statements can be specified in a single CASE expression.

THEN {value-expression|NULL}:

Specifies, in the form of a value expression, the value to return as the result if the specified search condition is TRUE. Specify NULL if you want to return the null value.

ELSE {value-expression|NULL}:

Specifies, in the form of a value expression, the value to return as the result if none of the search conditions specified in the WHEN statement is TRUE. Specify NULL if you want to return the null value.

If the ELSE specification is omitted, it is the same as specifying NULL in the ELSE statement.

To Page Top

(3) Rules

1. If multiple WHEN statements are specified in a CASE expression and more than one search conditions is TRUE, the result of the CASE expression will be the result of the first WHEN statement whose search condition is TRUE.

2. The results of the value expressions specified in THEN and ELSE must be data types that can be compared. For details about data types that can be compared, see (1) Data types that can be compared in 6.2.2 Data types that can be converted, assigned, and compared.

   However, note the following exceptions:

   • Date data cannot be compared to character string data (even to the predefined input representation of a date).

   • Time data cannot be compared to character string data (even to the predefined input representation of a time).

   • Time stamp data cannot be compared to character string data (even to the predefined input representation of a time stamp).

   For information about predefined input representations, see 6.3.3 Predefined character-string representations.

3. The data type and data length of the result of the CASE expression are determined by the data type and data length of the result of the value expression specified in the THEN or ELSE that corresponds to the search condition that was satisfied. For details, see 7.20.2 Data types of the results of value expressions.

4. A value expression must be specified for at least one THEN or the ELSE. You cannot specify NULL for every value expression, as illustrated below.

   Example:

     CASE
         WHEN "C1"=100 THEN NULL
         ELSE NULL
     END

5. A dynamic parameter cannot be specified by itself in the value expression in CASE, THEN, or ELSE.

6. The data type of the result of a CASE expression is without the NOT NULL constraint (the null value is allowed).

To Page Top

(4) Examples

Example 1

This example shows how to perform the following retrieval from table T1:

• If column C1 is 200: The retrieval result is value of column C2 + 20.

• If column C1 is 100: The retrieval result is value of column C2 + 10.

• If column C1 is a value other than 100 or 200: The retrieval result is value of column C2 + 5

SELECT "C1","C2",CASE WHEN "C1"=200 THEN "C2"+20
                      WHEN "C1"=100 THEN "C2"+10
                      ELSE "C2"+5
                 END AS "CASE"
    FROM "T1"

Example 2

This example shows how to search the employee table (EMPLIST), as follows:

• Determine the number of men and number of women in each section (SCODE).

SELECT "SCODE",SUM(CASE WHEN "SEX"='M' THEN 1 ELSE 0 END) AS "Men",
        SUM(CASE WHEN "SEX"='F' THEN 1 ELSE 0 END) AS "Women"
    FROM "EMPLIST"
    GROUP BY "SCODE"

Example 3

This example shows how to insert a row from the products table (PRODUCTLIST) into the new products table (PRODUCTLIST_NEW). When inserting the row, change the product prices (PRICE) as follows:

• If the product code (PCODE) is P001: reduce the price by 10%

• If the product code is P002: reduce the price by 20%

• Otherwise: reduce the price by 30%

INSERT INTO "PRODUCTLIST_NEW"("PCODE","PRICE")
    SELECT "PCODE",CASE WHEN "PCODE"='P001' THEN "PRICE"*0.9
                        WHEN "PCODE"='P002' THEN "PRICE"*0.8
                        ELSE "PRICE"*0.7
                   END
    FROM "PRODUCTLIST"

To Page Top