Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Wednesday, February 27, 2013

Oracle PL/SQL: Coalesce Function


In Oracle/PLSQL, the coalesce function returns the first non-null expression in the list. If all expressions evaluate to null, then the coalesce function will return null.

Syntax

The syntax for the coalesce function is:
coalesce( expr1, expr2, ... expr_n )
expr1 to expr_n are the expressions to test for non-null values.

Applies To

  • Oracle 11g, Oracle 10g, Oracle 9i

For Example

You could use the coalesce function in an SQL statement as follows:
SELECT coalesce( address1, address2, address3 ) result
FROM suppliers;
The above coalesce statement is equivalent to the following IF-THEN-ELSE statement:
IF address1 is not null THEN
   result := address1;

ELSIF address2 is not null THEN
   result := address2;

ELSIF address3 is not null THEN
   result := address3;

ELSE
   result := null;

END IF;
The coalesce function will compare each value, one by one.

No comments:

Post a Comment