4.9.2.8 CASE (SQL-92 Compatible)

Syntax

case-expr::
searched-case-expr | simple-case-expr

searched-case-expr::
CASE
     WHEN search_condition THEN { result-expr | NULL }
     [ ... ]
     [ ELSE expr | NULL ]
END

simple-case-expr::
CASE primary-expr
     WHEN expr THEN { result-expr | NULL }
     [ ... ]
     [ ELSE expr | NULL ]
END

Description

The CASE scalar function is a type of conditional expression. (See the topic on Conditional Expressions for a summary of all the conditional expressions.)

The general form of the CASE scalar function specifies a series of search conditions and associated result expressions. It is called a searched case expression. SQL returns the value specified by the first result expression whose associated search condition evaluates as true. If none of the search conditions evaluate as true, the CASE expression returns a null value (or the value of some other default expression if the CASE expression includes the ELSE clause).

CASE also supports syntax for a shorthand notation, called a simple case expression, for evaluating whether one expression is equal to a series of other expressions.

Notes

• This function is not allowed in a GROUP BY clause
• Arguments to this function cannot be query expressions

Arguments

CASE

The CASE keyword alone, not followed by primary-expr, specifies a searched case expression. It must be followed by one or more WHEN-THEN clauses each that specify a search condition and corresponding expression.

WHEN search_condition THEN { result-expr | NULL }

WHEN clause for searched case expressions. SQL evaluates search condition. If search_condition evaluates as true, CASE returns the value specified by result-expr (or null, if the clause specifies THEN NULL).

If search_condition evaluates as false, SQL evaluates the next WHEN-THEN clause, if any, or the ELSE clause, if it is specified.

CASE primary-expr

The CASE keyword followed by an expression specifies a simple case expression. In a simple case expression, one or more WHEN-THEN clauses specify two expressions.

A simple case expression can always be expressed as a searched case expression. Consider the following general simple case expression:

CASE primary-expr
     WHEN expr1 THEN result-expr1
     WHEN expr2 THEN result-expr2
     ELSE expr3
END

The preceding simple case expression is equivalent to the following searched case expression:

CASE
     WHEN primary-expr = expr1 THEN result-expr1
     WHEN primary-expr = expr2 THEN result-expr2
     ELSE expr3
END

WHEN expr THEN { result-expr | NULL }

WHEN clause for simple case expressions. SQL evaluates expr and compares it with primary-expr specified in the CASE clause. If they are equal, CASE returns the value specified by result-expr (or null, if the clause specifies THEN NULL).

If expr is not equal to primary-expr, SQL evaluates the next WHEN-THEN clause, if any, or the ELSE clause, if it is specified.

[ ELSE { expr | NULL } ]

In both searched case expressions and simple case expressions, the ELSE clause specifies an optional expression whose value SQL returns if none of the conditions specified in WHEN-THEN clauses were satisfied. If the CASE expression omits the ELSE clause, it is the same as specifying ELSE NULL.

Examples

The following example shows a searched case expression that assigns a label denoting tables as system tables if they begin with the letters sys. Note that this example can not be reformulated as a simple case expression, since it specifies a relational operator other than =.

SELECT tbl,
       CASE
          WHEN tbl like 'sys%' THEN 'System Table'
          ELSE 'Not System table'
       END
FROM systables;

TBL                                SEARCHED_CASE(TBLSY
---                                -------------------
systblspaces                       System Table
systables                          System Table
syscolumns                         System Table
sysindexes                         System Table
sysdbauth                          System Table
systabauth                         System Table
syscolauth                         System Table
sysviews                           System Table
syssynonyms                        System Table
sysdblinks                         System Table
sys_keycol_usage                   System Table
sys_ref_constrs                    System Table
sys_chk_constrs                    System Table
sys_tbl_constrs                    System Table
sys_chkcol_usage                   System Table
sysdatatypes                       System Table
syscalctable                       System Table
systblstat                         System Table

The following example shows a searched case expression and an equivalent simple case expression.

– Searched case expression:
SELECT tbl,
                 CASE
                     WHEN tbltype = 'S' THEN 'System Table'
                     ELSE 'Not System table'
                 End
FROM systables;
– Equivalent simple case expression:
SELECT tbl,
                 CASE tbltype
                     WHEN  'S' THEN 'System Table'
                     ELSE 'Not System table'
                 END
FROM systables;