CASE: SQL Case Operator

How to:

The CASE operator allows a value to be computed depending on the values of expressions or the truth or falsity of conditions.


Top of page

x
Syntax: How to Use the SQL Case Operator

In the first format below the value of test-expr is compared to value-expr-1, ..., value-expr-n in turn:

In the second format below the values of cond-1, ..., cond-n are evaluated in turn.

Format 1

CASE test-expr 
   WHEN value-expr-1 THEN result-expr-1 
   . . .
   WHEN value-expr-n THEN result-expr-n 
   [ ELSE else-expr ] 
END

Format 2

CASE
   WHEN cond-1 THEN result-expr-1 
   . . .
   WHEN cond-n THEN result-expr-n 
   [ ELSE else-expr ] 
END

where:

test-expr

Any type

Is the value to be tested in Format 1.

value-expr1, ... , value-expr-n

Any type of compatible with test-expr.

Are the values test-expr is tested against in Format 1.

result-expr1, ... , result-expr-n

Any type

Are the values that become the result value if:

  • The corresponding value-expr matches test-expr (Format 1).

or

  • The corresponding cond is true (Format 2).

The result expressions must all have a compatible type.

cond-1, ..., cond-n

Condition

Are conditions that are tested in Format 2.

else-expr

Any type

Is the value of the result if no matches are found. Its type must be compatible with the result expressions.

This operator returns the compatible type of the result expressions.



Example: Using the SQL Case Operator

CASE returns values based on expressions. This example,

CASE COUNTRY 
   WHEN 'ENGLAND' THEN 'LONDON'
   WHEN 'FRANCE' THEN 'PARIS'
   WHEN 'ITALY' THEN 'ROME'
   ELSE 'UNKNOWN'
END

returns LONDON when the value is ENGLAND, PARIS when the value is FRANCE, ROME when the value is ITALY, and UNKNOWN when there is no match.


iWay Software