CASE

Data Types and SubtypesData Definition (DDL) Statements

`CASE`

The CASE construct returns a single value from a number of possible values.Two syntactic variants are supported:

The simple CASE, comparable to a case construct in Pascal or a switch in C
The searched CASE, which works like a series of “if … else if … else if” clauses.

Simple `CASE`

Syntax

...
CASE <test-expr>
  WHEN <expr> THEN <result>
  [WHEN <expr> THEN <result> ...]
  [ELSE <defaultresult>]
END
...

When this variant is used, test-expr is compared to the first expr, second expr and so on, until a match is found, and the corresponding result is returned.If no match is found, defaultresult from the optional ELSE clause is returned.If there are no matches and no ELSE clause, NULL is returned.

The matching works as the “=” operator.That is, if test-expr is NULL, it does not match any expr, not even an expression that resolves to NULL.

The returned result does not have to be a literal value: it might be a field or variable name, compound expression or NULL literal.

Example

SELECT
  NAME,
  AGE,
  CASE UPPER(SEX)
    WHEN 'M' THEN 'Male'
    WHEN 'F' THEN 'Female'
    ELSE 'Unknown'
  END GENDER,
RELIGION
    FROM PEOPLE

A short form of the simple CASE construct is the DECODE function.

CASE

CASE

Simple CASE

`CASE`

Simple `CASE`