Using CASE Statements in Oracle

Introduction

With the introduction of Oracle9i, and Oracle's support for SQL:99, you can now perform If ... Then ... Else logic within SQL statements without having to use programming logic. (i.e. Java, PL/SQL). SQL:99 provides four types of CASE statements, all of which are described in this document.

---

The Simple CASE expression

The simple CASE expression was first introduced and available in 8.1.7 SQL, but NOT in PL/SQL. This type of expression is very similar to the DECODE statement (value and substitution expression pairs). The simple CASE expression enables users to specify a number of WHEN ... THERE ... pairs. The following is the general format used:

CASE expression
    WHEN comparison_expression1 THEN use_expression1
    WHEN comparison_expression2 THEN use_expression2
    WHEN comparison_expression3 THEN use_expression3
    ELSE default_use_expression
END

In the above statement, for each WHEN clause, Oracle will perform the use_expression if the CASE expression if equal to the comparison_expression. Only one use_expression will be executed; it will either be the first one associated with a true comparison_expression or it will be the default_use_expression. Keep in mind that you cannot use the NULL literal value to specify the use_expression or a default_use_expression. It is also possible to specify an ELSE clause, which is a "catchall" clause if none of the WHEN ... conditions are met and would therefore run the default_use_expression specified. If none of the comparison_expression1 are met and there is no ELSE clause specified, Oracle will return a NULL value.
Each CASE statement can have up to 255 arguments, and each WHEN ... THEN ... pair counts as 2 arguments. With this limit, you can have 127 WHEN ... THEN ... pairs and one ELSE expression in one CASE expression. CASE expressions can be nested as well.

SELECT
    emp_name AS "Employee Name"
  , (CASE dept_id
       WHEN 100 THEN 'Finance'
       WHEN 101 THEN 'Engineering'
       WHEN 103 THEN 'HR'
     ELSE
       'Not Assigned'
     END) AS "Department"
FROM employee;

Employee Name      Department
------------------ ------------
John Doe           Finance
Sarah Smith        Engineering
Jane Snow          Engineering
James Williams     HR

---

The Searched CASE expression

The next type of CASE expression is the searched CASE expression. The general format of the searched CASE expression is very similar to that of the simple CASE expression. The difference though, is that in a searched CASE expression, you specify a condition or predicate instead of a comparison_expression after the WHEN keyword. A predicate is the criteria that you use to specify in the WHERE clause of an SQL statement. Only one use_expression will be executed; it will either be the first one associated with a true condition or it will be the default_use_expression. It is important to note that even though more than one condition in the searched CASE expression may be true, Oracle stops at the first TRUE condition and performs that use_expression.
Here is the general format followed by a simple example of using the searched CASE expression:

CASE
    WHEN condition1 THEN use_expression1
    WHEN condition2 THEN use_expression2
    WHEN condition3 THEN use_expression3
    ELSE default_use_expression
END

SELECT
    emp_name AS "Employee Name"
  , (CASE
       WHEN UPPER(emp_name) LIKE 'JANE%' THEN 'This is Jane'
       WHEN emp_id > 102 THEN 'Out of range'
     ELSE
       'Not Assigned'
     END) AS "Department"
FROM employee;


Employee Name             Department
------------------------- ------------
John Doe                  Out of range
Sarah Smith               Out of range
Jane Snow                 This is Jane
James Williams            Out of range

---

The NULLIF Function
The general format for the NULLIF function is:

NULLIF(expression1, expression2)

If expression1 is equal to expression2, Oracle will return a NULL value; otherwise, it returns expression1. The SQL:99 syntax does not allow you to specify the NULL literal value for expression1. Keep in mind that you can use the following searched CASE expression to perform the same task. Oracle will return a NULL value when nothing is met in the CASE statement:

CASE
    WHEN expression1 != expression2 THEN expression1
END