Progress
SQL-92
Guide and Reference
CASE Function
Specifies a series of search conditions and associated result expressions. The general form 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 evaluates 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.
SYNTAX
searched_case_expr
simple_case_expr
CASE
Specifies a searched case expression. It must be followed by one or more
WHEN-THEN
clauses, each specifying a search condition and corresponding expression.WHEN search_condition THEN { result_expr | NULL }
Specifies a search condition and corresponding expression. SQL evaluates search_condition. If
search_condition
evaluates as true,CASE
returns the value specified by result_expr, or null, if the clause specifiesTHEN NULL
.If
search_condition
evaluates as false, SQL evaluates the nextWHEN-THEN
clause, if any, or theELSE
clause, if it is specified.CASE primary_expr
Specifies a simple case expression. In a simple case expression, one or more
WHEN-THEN
clauses specify two expressions.WHEN expr THEN { result_expr | NULL }
Prompts SQL to evaluate
expr
and compare it withprimary_expr
specified in theCASE
clause. If they are equal,CASE
returns the value specified byresult_expr
(or null, if the clause specifiesTHEN NULL
).If
expr
is not equal toprimary_expr
, SQL evaluates the nextWHEN-THEN
clause, if any, or theELSE
clause, if it is specified.ELSE { expr | NULL }
Specifies an optional expression whose value SQL returns if none of the conditions specified in
EXAMPLESWHEN-THEN
clauses are satisfied. If theCASE
expression omits theELSE
clause, it is the same as specifyingELSE NULL
.A simple case expression can always be expressed as a searched case expression. This example illustrates a simple case expression:
The simple case expression in the preceding CASE example is equivalent to the following searched case expression:
The following example shows a searched case expression that assigns a label denoting suppliers as ’In Mass’ if the state column value is ’MA’:
The following example shows the equivalent simple case expression:
NOTES
COMPATIBILITY
SQL-92 Compatible
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |