Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Apr 27, 2004

Oracle's CASE Expression - Page 2

By Amar Kumar Padhi

COALESCE and NULLIF functions

Oracle provides two more functions that carry out a functionality that is similar to the CASE expression in certain scenarios. We can use these in conjunction with or as a variety of the CASE expression.

COALESCE returns the first not null value in a given list of values.

E.g.: Returning the first not null value available in four columns present in a table.

select coalesce(col1, col2, col3, col4)
from am25;

E.g.: The above example will return the same result as the below statement with the CASE expression.

select case when col1 is not null then col1
            when col2 is not null then col2
            when col3 is not null then col3
            when col4 is not null then col4
            else null
       end VAL
from   am25;

The NULLIF function compares two values and does the following.

  • Returns null if both values are the same.
  • Returns the first value if both values are different.

E.g.: Returning the credits available for customers. The query below will return null if the TOTAL_CREDITS column is equal to the CREDITS_USED column for a customer, else it will return the TOTAL_CREDITS value.

select customer_name,  nullif(total_credit, credits_used)
from   customer_credits;

E.g.: The above example will return the same result as the statement below with CASE expression.

select customer_name, case when total_credits = credits_used then null 
                           else total_credits
from    customer_credits;


The maximum number of arguments that can be specified is 255, each WHEN ... THEN pair is counted as two arguments. To avoid this limitation the CASE function can be nested.

This functionality is supported in PL/SQL from Oracle 9i. The CASE expression will make it easy for developers to get more information based on analysis in a single query.

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM