Using the 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.

Case Expression Examples

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

Simple Case Expression

Give a textual description to the DISPLAY_ORDER column and display them in the numeric order:

 SELECT category_id,
        CASE display_order
           WHEN 10 THEN 'FIRST'
           WHEN 20 THEN 'SECOND'
           WHEN 30 THEN 'THIRD'
           WHEN 40 THEN 'FOURTH'
           ELSE 'UNKNOWN'
        END,
        format
    FROM formats
    ORDER BY category_id, display_order, format;

Returns:

CATEGORY_ID		FORMAT
1	FIRST	DVD Audio
1	SECOND	Audio CD
1	THIRD	Cassette
1	FOURTH	Vinyl
2	FIRST	Hardcover
2	SECOND	Paperback
2	THIRD	Audio CD
2	FOURTH	Audio Cassette
3	FIRST	DVD Video
3	SECOND	Video

This form of a case expression is known as a simple case expression, in which an operand 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.

Display the word "UNKNOWN" if the EXCHANGE_RATE value is undefined (i.e. NULL); and display the word "PARITY" if the rate is 1 to 1; otherwise do not display anything:

 SELECT currency,
        CASE
           WHEN exchange_rate IS NULL THEN 'UNKNOWN'
           WHEN exchange_rate = 1.0 THEN 'PARITY'
           ELSE ''
        END
    FROM currencies
    WHERE code LIKE 'A%';

Returns:

CURRENCY
UAE Dirhams
Afghanis
Leke
Armenian Drams	UNKNOWN
Netherlands Antillian Guilders
Kwanza	UNKNOWN
...	...

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.

Mimer Information Technology AB Voice: +46 18 780 92 00 Fax: +46 18 780 92 40 info@mimer.se