FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 
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.

Searched CASE
Syntax
CASE
  WHEN <bool_expr> THEN <result>
  [WHEN <bool_expr> THEN <result> ...]
  [ELSE <defaultresult>]
END

The bool_expr expression is one that gives a ternary logical result: TRUE, FALSE or NULL.The first expression to return TRUE determines the result.If no expressions return TRUE, defaultresult from the optional ELSE clause is returned as the result.If no expressions return TRUE and there is no ELSE clause, the result will be NULL.

As with the simple CASE construct, the result need not be a literal value: it might be a field or variable name, a compound expression, or be NULL.

Example
CANVOTE = CASE
  WHEN AGE >= 18 THEN 'Yes'
  WHEN AGE < 18 THEN 'No'
  ELSE 'Unsure'
END