COALESCE#

Functionality:#

This Oracle function will return the first non-null value in a list of values (or NULL if all values are null)

Parameters:#

FirstValue	The first value you want to test for NULL
SecondValue	If the first value, is null then test this value for NULL

etc.

Returns: #

value of the type you are specifying

Errors: #

None

Operations: #

This function will take as many parameters as you want, and will just proceed through the values supplied in order, until it finds a non null value.

Example:#

COALESCE(easd_hours,dwr_hours,dps_hours,djb_hours,dgr_hours)

will look at the first variable's value and if it is not null, return that; if it is null, it will go to the second variable and examine its value. If it is not null, it will return that, otherwise it will go to the third variable and examine its value (and so on) until the final value which will be returned (even if it is NULL). This has the same effect, but is much more performant than

NVL(easd_hours,NVL(dwr_hours,NVL(dps_hours,NVL(djb_hours,dgr_hours))))

especially when there is many values to test. It also has the same effect as a CASE statement that compares each of the values.

---

Notes #

Click to create a new notes page