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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

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
                      end
from    customer_credits;

Conclusion

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

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date