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 ] END

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.

END

Specifies the end of case expression.

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