Mimer SQL Documentation TOC PREV NEXT INDEX

Mimer SQL Developer Site


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
    GROUP 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 against the euro; 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
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
info@mimer.se
Mimer SQL Documentation TOC PREV NEXT INDEX