The CASE statements are used to implement a complex conditional construct. The CASE statements are declared in two ways:

CASE caseValue

   WHEN conditionValue THEN statements;

   WHEN conditionValue THEN statement...;

   ELSE statements;

END CASE

Or:

CASE

   WHEN Condition THEN statements;

   WHEN Condition THEN statements...;

   ELSE statements;

END CASE

In the first type of syntax, the caseValue is checked against conditionValue. When the condition is satisfied, the corresponding statements are executed. If none of the conditions are satisfied, the ELSE statements are executed.

In the second type of syntax, the condition after WHEN is evaluated, if it is true, the statements are executed. If no condition is true, the ELSE statements are executed.

The ELSE should not have empty statement lists. When we are comparing single expression against a range of unique values the CASE statements are useful.

Examples

The examples of the CASE statements is given below:

Example 1:

SELECT

   CASE (40%2) 

      WHEN 0 THEN 'number even';

      ELSE 'number odd';

   END

Above example generates the output as 'number even'.

Example 2:

CASE

   WHEN ('A'='a') THEN 'String equal';

   ELSE 'They are unequal';

END

Above example generates the output as "String equal".