function, a more generic form of NVL
, returns the first non-null expression in the argument list.
It takes minimum two mandatory parameters but maximum arguments has no limit.
COALESCE (expr1, expr2, ... expr_n )
Consider the below SELECT
query. It selects the first not null value fed into address fields for an employee.
SELECT COALESCE (address1, address2, address3) Address
The working of COALESCE
function is similar to IF..ELSIF..ENDIF
construct. The query above can be re-written as:
IF address1 is not null THEN
result := address1;
ELSIF address2 is not null THEN
result := address2;
ELSIF address3 is not null THEN
result := address3;
result := null;
Read more: Conv. Func.