CASE Expressions

CASE expressions let you use IF ... THEN ... ELSE logic in expressions.

In a simple case expression, CASE searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr, then it returns return_expr. If none of the WHEN ... THEN pairs meet this condition, and an ELSE clause exists, then CASE returns else_expr. Otherwise, CASE returns null.

In a searched CASE expression, CASE searches from left to right until it finds an occurrence of condition that is true, and then returns return_expr. If no condition is found to be true, and an ELSE clause exists, CASE returns else_expr. Otherwise, CASE returns null.

Return Value

Same as the else_expression argument

Syntax

CASE { simple_case_expression
     | searched_case_expression
     }
     [ ELSE else_expression ]
     END

simple_case_expression::=
     expr WHEN comparison_expr
          THEN return_expr
          [ WHEN comparison_expr
            THEN return_expr ]...

searched_case_expression::=
     WHEN condition THEN return_expr
     [ WHEN condition THEN return_expr ]...

Arguments

expr is the base expression being tested.

comparison_expr is the expression against which expr is being tested. It must be the same basic data type (numeric or text) as expr.

condition is a conditional expression.

return_expr is the value returned when a match is found or the condition is true.

Example

The following statement returns Low, Medium, or High depending on the value of credit_limit:

CASE credit_limit WHEN 100 THEN 'Low'
   WHEN 5000 THEN 'High'
   ELSE 'Medium'

The next statement returns the actual salary if it is greater than or equal to 2000, or 2000 if it is less than that.

CASE WHEN salary > 2000 THEN salary
   ELSE 2000 END