CASE
Go Up to Statement and Function Reference (Language Reference Guide)
The CASE function allows you to evaluate a column value on a row against multiple criteria, where each criterion might return a different value.
CASE <expression>
WHEN <expression> THEN <expression> | NULL
[ELSE <expression> | NULL]
[COALESCE <expression>]
[NULLIF <expression, expression, ...>]
END
Description: The CASE expression is a conditional value expression that consists of a list of value expressions, each of which is associated with a conditional expression. A CASE expression evaluates to the first value expression in the list for which its associated conditional expression evaluates to TRUE. The CASE expression has simple and searched forms of syntax.
The COALESCE and NULLIF expressions are common, shorthand forms of use for the CASE expression involving the NULL state. A COALESCE expression consists of a list of value expressions. It evaluates to the first value expression in the list that evaluates to non-NULL. If none of the value expressions in the list evaluates to non-NULL, then the COALESCE expression evaluates to NULL.
The NULLIF expression consists of a list of two value expressions. If the two expressions are unequal then the NULLIF expression evaluates to the first value expression in the list. Otherwise, it evaluates to NULL.
Example: The following example demonstrates the use of CASE using the sample employee.ib database:
SELECT emp.first_name || ' ' || emp.last_name AS NAME,
CASE proj.proj_name
WHEN 'DigiPizza' THEN 'Digital Pizza'
WHEN 'AutoMap' THEN 'AutoMobile Map'
WHEN 'Translator upgrade' THEN 'Universal Language Translator'
ELSE 'Other'
END
AS project
FROM employee emp
INNER JOIN employee_project emp_proj
ON emp.emp_no = emp_proj.emp_no
INNER JOIN project proj
ON emp_proj.proj_id = proj.proj_id