CASE function (SQL)

Introduction

The SQL CASE function selects one result from a set of results based on a list of conditions.

The Case function has two different formats:

Syntax

primary-case ::=
  simple-case
  | searched-case

simple-case ::=
  CASE simple-case-selector simple-when-clause... [ else-clause ] END

simple-case-selector ::=
  value-expression

simple-when-clause ::=
  WHEN when-operand-list THEN result

when-operand-list ::=
  when-operand [ { ',' when-operand } ]

when-operand ::=
  value-expression
  | comparison-predicate-part2
  | between-predicate-part2
  | in-predicate-part2
  | null-predicate-part2
  | like-predicate-part2

searched-case ::=
  CASE searched-when-clause... [ else-clause ] END

searched-when-clause ::=
  WHEN search-condition THEN result

else-clause ::=
  ELSE result

result ::=
  value-expression

Remarks

The simple-case function evaluates the simple-case-selector and compares the result against each WHEN clause. The value returned from the CASE function is the result from the first matching WHEN clause. If no WHEN clause matches, the value associated with the ELSE clause is returned.

If the selector value in a simple-case is NULL, the Case function returns NULL.

The searched-case function evaluates the condition of each WHEN clause until a match is found. The value returned from the CASE function is the result from the first WHEN clause with a true condition. If no WHEN clause matches, the value associated with the ELSE clause is returned.

If the ELSE clause is omitted, then ELSE NULL is implied.

Further Information

Value Expressions (SQL)

To learn more about SQL scalar value expressions.

Fernhill SCADA Structured Query Language (SQL)

To learn more about the SQL features in Fernhill SCADA.

Glossary

For definitions of the terms used in Fernhill SCADA.