Background documentationSimple CASE Function (simple_case_function) Locate this document in the navigation structure

 

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.

Structure

Syntax Syntax

  1. <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>
End of the code.
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 match is found, CASE returns the result of default_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 have 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.