Sum of Nulls- Aggregator

wahi80 · Post by **wahi80** » Fri Sep 19, 2008 12:58 pm

Hi,

I have a job as follows

InDataset-->Aggregator---> OutDataset

The InDataset has three fields a,b,c. In the aggregator the group by is on a,b and summation of c. C can have values, Null,0,+ve or -ve numbers.

The problems is that all sum of nulls is converted to 0 in OutDataset. Hence I cannot figure out if the 0 is due to nulls or due to summation.

In Oracle a sum of nulls returns null, but not in DS.

Any way how to figure this out?

Regards
Ankur

csri · Post by **csri** » Fri Sep 19, 2008 1:11 pm

In oracle also you will get column c sum as null only if all the records have null values. Anyways, one way to try this is have an additional field derived based on the value of column c. Lets say column d. Set the column d with NULVALUE if null or NOTNULVALUE is not null or ZEROVALUE if zero and passit through the aggregrator. Now you have to group by a,b,d and sum c.

Hope this help. Let me know if this works.

ambasta · Post by **ambasta** » Fri Sep 19, 2008 10:12 pm

The output of Aggregator stage is always of double data type and as per my knowledge there is no concept of null in Double data Type .so you will never get Null as output.. Just to help your requirement ..Change the Null to some diffrent character before sending it to aggregator stage and then proceed..