Oracle’s COALESCE function usage


COALESCE(expression1, expression2, expression(n)…)

–returns the first not NULL value in coalesce function list
–following example will return the address1 if is not null, or the second expression’s value will return
–if the address2 is NULL  then the last expression’s value ‘doesn’t have an address’ will return from the function

SELECT first_name, last_name, COALESCE(address1,address2,’doesn’t  have an address’ )
FROM employees
WHERE employee_id < 105;

FIRST_NAME           LAST_NAME                 HASANADDRESS                             
——————– ————————- —————————————-
Steven                     King                            doesn’t  have an address               
Neena                      Kochhar                    King St. …
Lex                          De Haan                     Main St. …                                      
Alexander                 Hunold                       Allday St. ..

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s