Search
CASE Function

CASE Function

Evaluates a list of conditions and returns one of the possible result expressions.

Syntax
The simple CASE function compares an expression to a set of simple expressions to determine the result.

CASE input_expression
  WHEN when_expression THEN result_expression
    [ ...n ]
  [
    ELSE else_result_expression
  ]
END

The searched CASE function evaluates a set of Boolean expressions to determine the result.

CASE
  WHEN Boolean_expression THEN result_expression
    [ ...n ]
  [
    ELSE else_result_expression
  ]
END

Arguments
input_expression

Is the expression evaluated when using the simple CASE format. input_expression is any valid expression.

WHEN when_expression

Is a simple expression to which input_expression is compared when using the simple CASE format. when_expression is any valid expression. The data types of input_expression and each when_expression must be the same or must be an implicit conversion.

[...n]

Is a placeholder indicating that multiple WHEN when_expression THEN result_expression clauses, or multiple WHEN Boolean_expression THEN result_expression clauses can be used.

THEN result_expression

Is the expression returned when input_expression equals when_expression evaluates to TRUE, or Boolean_expression evaluates to TRUE. result expression is any valid expression.

ELSE else_result_expression

Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.

WHEN boolean_expression

Is the Boolean expression evaluated when using the searched CASE format. Boolean_expression is any valid Boolean expression.

Result Values

Simple CASE function:

  • Evaluates input_expression, and then, in the order specified, evaluates input_expression = when_expression for each WHEN clause.
     
  • Returns the result_expression of the first (input_expression = when_expression) that evaluates to TRUE.
     
  • If no input_expression = when_expression evaluates to TRUE, returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

For example:

SELECT started_at, CASE started_at
    WHEN 0.01 THEN 'low'
    WHEN 1 THEN 'good'
    WHEN 10 THEN 'high'
    ELSE 'Unknown'
END
FROM [auction_info]

Searched CASE function:

  • Evaluates, in the order specified, Boolean_expression for each WHEN clause.
     
  • Returns result_expression of the first Boolean_expression that evaluates to TRUE.
     
  • If no Boolean_expression evaluates to TRUE, returns the else_result_expression if an ELSE clause is specified, or a NULL value if no ELSE clause is specified.

For example:

SELECT started_at, CASE
    WHEN started_at < 1 THEN 'low'
    WHEN started_at < 10 THEN 'good'
    WHEN started_at > 10 THEN 'high'
    ELSE 'Unknown'
END
FROM [auction_info]
Send Feedback