Mimer SQL User's Manual TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


Using CASE Expression


With a case expression, it is possible to specify a conditional value. Depending on the result of one or more conditional expressions, the case expression can return different values.

The rules for CASE expressions are fully described in the Mimer SQL Reference Manual.

Case Expression Examples

The following select statements presents two examples of how CASE expressions can be used.

Simple Case Expression

Translate the currency code in the exchange_rate table to descriptive names:
 SELECT CASE CURRENCY
          WHEN 'DEM' THEN 'German Marks' 
          WHEN 'DKK' THEN 'Danish Crowns' 
          WHEN 'FRF' THEN 'French Francs'
          WHEN 'GBP' THEN 'British Pounds'
          WHEN 'ITL' THEN 'Italian Lira'
          ELSE CURRENCY 
        END  AS CURRENCY, RATE
 FROM   EXCHANGE_RATE;
Result:

 CURRENCY
 RATE
 German Marks
 0.223
 Danish Crowns
 0.849
 FIM
 0.656
 French Francs
 0.742
 British Pounds
 0.081
 Italian Lira
 206.820
 JPY
 16.380
 NOK
 0.881
 SEK
 1.000
 USD
 0.133

This form of a case expression is known as a simple case expression, in which an operand (CURRENCY in this case) is compared to a list of values.

If there is a match in one of the when clauses, the result is the value to the right of the then clause.

If none of these matches, the value in the else clause is returned.

If there is no else clause in a case expression and no when clause matches, a null value is returned.

Case Expression

The other form of the case expression can be seen in the following example.

Divide room prices into different categories.
 SELECT CASE
          WHEN PRICE >= 900 then 'Expensive'
          WHEN PRICE <= 700 then 'Budget' 
          ELSE 'Moderate'
        END  AS CATEGORY, ROOMTYPE, PRICE
 FROM   ROOM_PRICES;
Result:

 CATEGORY
 ROOMTYPE
 PRICE
 Expensive
 NSDBLB
 900
 ...
 
 
 Budget
 NSSGLB
 660
 ...
 
 
 Moderate
 SDBLB
 830
 ...
 
 

In this form it is possible that more than one of the when clauses evaluates to true, in which case the value in the first (from left) of the matching clauses is returned.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL User's Manual TOC PREV NEXT INDEX