Oracle CASE statement

In Oracle statement CASE has IF-THEN-ELSE functionality. Starting with Oracle 9i, you can use a CASE statement in an SQL sentence.

Syntax of CASE statement

CASE [ ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
…
WHEN condition_n THEN result_n

ELSE result

END

Parameters and arguments of the CASE statement

• expression is not mandatory. It is a value that you compare to conditions (that is: condition_1, condition_2 … condition_n).
• condition_1 … condition_n must be of the same type. Conditions are evaluated in order, one after another. After the condition takes the value TRUE (truth), the CASE operator returns the result and will not evaluate the conditions further.
• result_1 … result_n all must be of the same data type. This value is returned once the condition assumes TRUE (True).

Note:

• If the condition does not accept TRUE, the CASE operator will return the ELSE offer.
• If the ELSE offer is omitted and the condition does not accept TRUE, the CASE operator will return NULL.
• The CASE operator can have up to 255 comparisons. Each WHEN … THEN sentence considers 2 comparisons.

You can use the CASE statement in the following versions of Oracle / PLSQL:

You can use a CASE statement in an SQL sentence as follows:

SELECT table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'.
END
FROM all_tables;

Or you could write an SQL query using the CASE statement as follows: (omitting WHEN … THEN in the sentence). For example:

SELECT table_name,
CASE
WHEN owner='SYS' THEN 'The owner is SYS'
WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
ELSE 'The owner is another value'.
END
FROM all_tables;

These two examples of CASE statement are equivalent to the following conditional IF-THEN-ELSE statement:

IF owner = 'SYS' THEN
result := 'The owner is SYS';
ELSIF = 'SYSTEM' THEN
result := 'The owner is SYSTEM';
ELSE
result := 'The owner is another value';
END IF;

The CASE operator will compare each value of the owner, one after the other.

The ELSE offer in a CASE operator is optional. It can be omitted. Let’s look at a previous SQL query with the ELSE clause omitted.

The SQL query will look like this:

SELECT table_name,
CASE owner
WHEN 'SYS' THEN 'The owner is SYS'
WHEN 'SYSTEM' THEN 'The owner is SYSTEM'
END
FROM all_tables;

With no ELSE offer, if none of the conditions accept TRUE, the CASE operator will return NULL.

Comparing 2 conditions

Here is an example that shows how to use a CASE operator to compare different conditions:

SELECT
CASE
WHEN a < b THEN 'hello'
WHEN d < e THEN 'goodbye'
END
FROM suppliers;

Comparison of 2 fields in one CASE sentence:

SELECT supplier_id,
CASE
WHEN supplier_name = 'IBM' and supplier_type = 'Hardware' THEN 'North office'
WHEN supplier_name = 'IBM' and supplier_type = 'Software' THEN 'South office'
END
FROM suppliers;

So, if the field supplier_name = ‘IBM’ and field supplier_type = ‘Hardware’, the CASE operator will return ‘North office’. If the field supplier_name = ‘IBM’ and supplier_type = ‘Software’, the CASE operator will return ‘South office’.

CASE STATEMENT(IF THEN ELSE) IN ORACLE SQL WITH EXAMPLE