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;
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.
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;
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