AntDB-Oracle Compatibility Developer’s Manual P3–66

COALESCE

The COALESCE function returns the first non-null value in the argument. The null value is returned only if all the arguments are null.

COALESCE(value [, value2 ] ...)

When it is necessary to display the extracted value, we usually use the default value instead of the null value.

select COALESCE(value1 , value2, default_value)…

As with CASE expressions, COALESCE does not compute parameters that are not needed to determine the result. This means that the arguments to the right of the first non-null argument are not computed. This SQL standard function provides similar functionality to the functions NVL and IFNULL, which can be used in other database systems.

Example:

create table test(id int, math int,chinese int, english int); insert into test(id, math) values(1,88); insert into test(id, english) values(2,98); insert into test(id, chinese) values(3,97); insert into test(id) values(4); SELECT COALESCE(math, chinese, english, 0) from test; COALESCE ------------------- 88 98 97 0 (4 rows) SELECT COALESCE(math, chinese, english) from test; COALESCE ------------------ 88 98 97 (4 rows)