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 

searched_case_expr

SYNTAX
CASE
  WHEN search_condition THEN { result_expr | NULL }
  [  ...  ]
  [ ELSE expr | NULL ]
END 

simple_case_expr

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.

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 lastname, city,
     case
          when state = ’MA’ then ’In Mass’ else ’Not in Mass’
     end
from supplier;
 
Lastname         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 lastname, city 
     case state 
          when ’MA’ then ’In Mass’ else ’Not in Mass’ 
     end 
FROM supplier;
 
Lastname         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          

NOTES

COMPATIBILITY

SQL-92 Compatible


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095