Try OpenEdge Now
skip to main content
SQL Reference
SQL Reference : OpenEdge SQL Functions : CASE
 

CASE

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_exprsimple_case_expr

Parameters

searched_case_expr
Uses the following syntax:
CASE
WHEN search_condition THEN {result_expr| NULL }[...]
[ ELSE expr| NULL ]END
simple_case_expr
Uses the following syntax:
CASE primary_expr WHEN expr THEN {result_expr| NULL }[...]
[ ELSE expr| NULL ]
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 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
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 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 }
Specifies an optional expression whose value SQL returns if none of the conditions specified in WHEN-THEN clauses are satisfied. If the CASE expression omits the ELSE clause, it is the same as specifying ELSE NULL.

Notes

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

Examples

A simple case expression can always be expressed as a searched case expression. This example illustrates a simple case expression:
CASE primary_expr
WHEN expr1 THEN result_expr1
WHEN expr2 THEN result_expr2
ELSE expr3
END
The simple case expression in the preceding CASE example 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
The following example shows a searched case expression that assigns a label denoting suppliers as 'In Mass' if the state column value is 'MA':
SELECT name, city,
CASE
WHEN state = 'MA' THEN 'In Mass' ELSE 'Not in Mass'
END
FROM supplier;
Name             City  searched_case(State,MA,In Mass,)
---------------- ----------------  --------------------------------
GolfWorld Suppl Boston In Mass
Pool Swimming S Valkeala Not in Mass
Nordic Ski Whol Hingham In Mass
Champion Soccer Harrow Not in Mass
ABC Sports Supp Boston In Mass
Seasonal Sports Bedford In Mass
Tennis Supplies Boston In Mass
Boating Supplie Jacksonville Not in Mass
Aerobic Supplie Newport Beach Not in Mass
Sports Unlimite Irving Not in Mass
The following example shows the equivalent simple case expression:
SELECT name, city,
CASE state
WHEN 'MA' THEN 'In Mass' ELSE 'Not in Mass'
END
FROM supplier;
Name             City               simple_case(State,MA,In Mass,)
---------------- ----------------   ------------------------------
GolfWorld Suppl Boston In Mass
Pool Swimming S Valkeala Not in Mass
Nordic Ski Whol Hingham In Mass
Champion Soccer Harrow Not in Mass
ABC Sports Supp Boston In Mass
Seasonal Sports Bedford In Mass
Tennis Supplies Boston In Mass
Boating Supplie Jacksonville Not in Mass
Aerobic Supplie Newport Beach Not in Mass
Sports Unlimite Irving Not in Mass

Compatibility

SQL compatible