|
|
|
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 Conditional Expressions for more details and 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. c-treeSQL 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
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. c-treeSQL 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, FairCom DB 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. c-treeSQL 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, c-treeSQL 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 FairCom DB 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 cannot 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;
