coalesce

1coalesce(value1 , value2 , value3 , ... )

The arguments must all be convertible to a common data type, which will be the type of the result.

This function is often used to substitute a default value for null values when data is retrieved for display. For example:

SELECT COALESCE(description, short_description, '(none)') ...

The above example  returns description if it is not null, otherwise short_description if it is not null, otherwise (none).

Example: Left Outer Cogroup with coalesce()

A left outer cogroup combines the right data stream with the left data stream. If a record on the left stream does not have a match on the right stream, the missing right value comes through as null. To replace null values with a different value, use coalesce().

For example, suppose that you have a dataset of meeting information from the Salesforce Event object, and you join it with data from the Salesforce Opportunity object. This shows amount won with the total time spent in meetings.

1ops = load "Ops";
2meetings = load "Meetings";
3q = cogroup ops by 'Account' left, meetings  by 'Company' ;
4q = foreach q generate ops.'Account' as 'Account', sum(ops.'Amount') as 'sum_Amount', sum(meetings.'MeetingDuration') as 'TimeSpent';

It looks like we had no meetings with Zen Retreats.

Let��s use coalesce() to change that null value to a zero.

1ops = load "Ops";
2meetings = load "Meetings";
3q = cogroup ops by 'Account' left, meetings  by 'Company' ;
4
5–-use coalesce() to replace null values with zero
6q = foreach q generate ops.'Account' as 'Account', sum(ops.'Amount') as 'sum_Amount', coalesce(sum(meetings.'MeetingDuration'), 0) as 'TimeSpent';