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

NULL in Expressions

NULL is not a value in SQL, but a state indicating that the value of the element either is unknown or it does not exist.It is not a zero, nor a void, nor an “empty string”, and it does not act like any value.

When you use NULL in numeric, string or date/time expressions, the result will always be NULL.When you use NULL in logical (Boolean) expressions, the result will depend on the type of the operation and on other participating values.When you compare a value to NULL, the result will be unknown.

Important

In SQL, the logical result unknown is also represented by NULL.

Consult theFirebird Null Guidefor more in-depth coverage of Firebird’s NULL behaviour.