DBA Tips Archive for Oracle


Using CASE Statements

by Jeff Hunter, Sr. Database Administrator


  1. Introduction
  2. The Simple CASE expression
  3. The Searched CASE expression
  4. The NULLIF Function
  5. The COALESCE Function


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
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.

    emp_name AS "Employee Name"
  , (CASE dept_id
       WHEN 100 THEN 'Finance'
       WHEN 101 THEN 'Engineering'
       WHEN 103 THEN 'HR'
       '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:

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

    emp_name AS "Employee Name"
  , (CASE
       WHEN UPPER(emp_name) LIKE 'JANE%' THEN 'This is Jane'
       WHEN emp_id > 102 THEN 'Out of range'
       '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:

    WHEN expression1 != expression2 THEN expression1

The COALESCE Function

Copyright (c) 1998-2018 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Saturday, 18-Sep-2010 17:53:11 EDT
Page Count: 18966