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, Chapter 8, 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;
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.
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%';
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