Edit

CASE (Azure Stream Analytics)

  • Article

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

The CASE expression has two formats:

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

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

Both formats require an ELSE argument.

CASE can be used in any statement or clause that allows a valid expression. For example, you can use CASE in expressions such as SELECT and in clauses such as WHERE and HAVING.

Syntax

Simple CASE expression:

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

Searched CASE expression:

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

Arguments

input_expression

Is the expression evaluated when the simple CASE format is used. The evaluated value is compared against the when_expression.

WHEN when_expression

Is the expression to which input_expression is compared when using the simple CASE format. The types of the when_expressions do not necessarily have to match.

WHEN boolean_expression

Is the boolean expression evaluated when using the searched CASE format. If this expression evaluates to true, then the corresponding result_expression is returned.

THEN result_expression

Is the expression returned when input_expression equals when_expression (in the simple CASE format) or when boolean_expression evaluates to true (in the searched CASE format).

ELSE else_result_expression

Is the expression returned if none of the conditions evaluated to TRUE.

Return type

Is the highest precedence type from the set of types in result_expression(s) and else_result_expression.

Examples

Using select with a simple CASE expression:

  SELECT
    CASE vehicleType
      WHEN 'S' THEN 'Sedan'
      WHEN 'T' THEN 'Truck'
      WHEN 'V' THEN 'Van'
      ELSE NULL
    END as vehicleTypeName
  FROM vehicles

Using select with searched CASE expression:

  SELECT
    CASE
      WHEN temperature < 60 THEN 'Alert'
      ELSE 'OK'
    END as currentStatus
  FROM sensor