Simple CASE Function (simple_case_function) 

The simple CASE function (simple_case_function) is a special function that compares an expression with a set of simple expressions to determine a result expression.

Syntax

<simple_case_function> ::=
CASE <check_expression>
        WHEN <search_expression> THEN <result_expression>
            [...]
        [ELSE <default_expression>])
END

<check_expression> ::= <expression>
<search_expression> ::= <expression>
<result_expression> ::= <expression>
<default_expression> ::= <expression>

Examples

SQL Tutorial, Functions

Explanation

CASE compares the value of the comparison expression check_expression with the values of the expressions search_expression, one after the other. If values match, the result of the simple CASE function is the value of the result_expression that belongs to the search_expression.

If no values match, CASE supplies the result of the default_expression expression. If default_expression is not specified, the result of CASE is the NULL value.

The data types of check_expression and search_expression must be comparable. The data types of result_expression and default_expression must be comparable. The data types of search_expression and result_expression do not need to be comparable.

If the values of the expressions check_expression and search_expression are NULL values, then a match exists. The comparison of the special NULL value with any other value never results in a match.

See also:

General CASE Function (searched_case_function)