Add two results of PU SUM

Hi ,

I need some help.

I want to add two results from PU_SUM ( child_table, parent_table.column [, filter_expression] ).

I wrote

ADD (PU_SUM ( child_table, parent_table.column [, filter_expression] ),PU_SUM ( child_table, parent_table.column [, filter_expression] ))

or

PU_SUM ( child_table, parent_table.column [, filter_expression] ) + PU_SUM ( child_table, parent_table.column [, filter_expression] )

But there is a rule: If no value in the parent table exists for the element in the child table (either because all values of the parent table column are filtered out, or because no corresponding value exists in the first place), NULL will be returned.

If the result of first of them = NULL, then the result from ADD is also NULL, although the 2nd result is <> NULL.

But I would like to have the sum of both. How should I do that?

Hi @kavya.kadiyala

The addition operator returns NULL when at least one of the inputs is NULL. However, you want a NULL input to behave like a 0. So you simply have to map all NULL-values that you get from the PU functions to 0 first before adding them.
For this, you can use the COALESCE function, which returns the result of the PU_SUM if it is not NULL, and 0 otherwise:

COALESCE( PU_SUM ( ... ) , 0) + COALESCE( PU_SUM ( ... ), 0)

Cheers
David