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