Aggregator and sum function

[mattias.klint]

Hi,

I have an aggregator that sums a field. In the hashed file before the aggregator the field beeing summed is Char. In the aggregtor it is decimal with 4 decimals. This seems to work fine.

The problems come when I after this has an transformer where I have two inputs, I match some fields, this works fine. If I have input from either feed in one cell, no problem. If I sum a field by itself in the out put from one feed, no problem.

The problems accure when I try to add one field from one feed to one field from the other feed. Dont work!!!

Explained in another way,
1 One feed from aggregator
2 One normal feed

FEED_1 can sum two fields, FIELD_1 + FIELD_1
FEED_2 can sum two fields, FIELD_1 + FIELD_1

FEED_1 and FEED_2 can not sum each other:
FEED_1_FIELD_1 + FEED2_FIELD_1

Very strange to me...

//Mattias, king of Chile!!!

[mattias.klint]

Or maybe I should put it like this, does anyone have any comments on summing fields with an aggregator?

;-)

[DSguru2B]

Welcome Aboard
Group by the key columns and provide Summation for the field you want to sum. This will sum up all the records corresponding to the grouping field in that particular column. If you want to add two columns you can do that in a transformer.

[mattias.klint]

Thats what I tried to do, add two columns from diffrent feeds, but I have to do it in a StageVariable, and there I get this error:

DataStage Job 123 Phantom 22318
Program "JOB.1371933176.DT.1426135363.TRANS1": Line 329, Improper data type.
Attempting to Cleanup after ABORT raised in stage R2_TBExpress_16_jan_2..TRF_MX_to_SBS_Match
DataStage Phantom Aborting with @ABORT.CODE = 3

I can add them just fine in the normal columns.

//Mattias

[mattias.klint]

Found out something more. If I put a FMT(FIELD,"R0") in the FEED that comes from my aggregator before my transformer the Stage variable adding the two fields (FEED_1.FIELD1 + FEED_2.FIEDL1) works fine.

Guess my question is getting more and more messy, hope you understand.

/Mattias who want to go to Sweden

[DSguru2B]

Using FMT with code R0 will round your input. Is that what you really want. What is the datatype of both of your columns? Need to know the sql type, the length and scale.

[mattias.klint]

I use Char in all the job, but the aggregator I have Decimal, therefore the two hashed files arround the aggregator also decimal. The decimal is length 100(just like all char) and the scale is 4. The hashed files around the aggregator is input(before) and output(after) char with length 100.

After the output from the hashed file is where i try to add it with the char input from another feed in the StageVariable.

When I use the R0 after the aggregator it works, but just like you say, I want four decimals. But using R4 or just leave it be it does not work.

Hope I made any sence,
Mattias

[DSguru2B]

Before sending it to the aggregator, change the sql type from char to decimal. You might want to re-think the length. You sure its 100. Thats way too much. Change it to float or decimal and scale to 4 as you say, for both the inputs. Then try adding them.

[mattias.klint]

Nope, does not work. What is the best way to change data types, I do it in the hasded files?


Test it buddy. It doesnt work due to improper datatypes as the error message states.
Do this, initialize the stage variable as 0.0000
See if that helps.[/quote]

[mattias.klint]

Found something not sure if it means anything. When my both feeds goes into the transformer to get summed, the lines that are 0 are not 0.0000 but only 0. Is this right? Even if I have put them to decimal, scale 4.

thanks a lot...


[quote="mattias.klint"]Nope, does not work. What is the best way to change data types, I do it in the hasded files?

[mattias.klint]

tried the same hashed file as input to the transformer and of course it worked and I guess this means that it is the data format.

//Mattias

[kcbland]

Never add NULLs, never send NULLs into the Aggregator. Always insure datatypes and turn to 0 if necessary.

[DSguru2B]

As Ken advised, handle nulls before sending your stream into the aggregator.

[mattias.klint]

Nope no nulls. But a question. Is there a difference if I enter a 0 or a "0" in a char field?

[DSguru2B]

No. It will not matter. The target sql type will decide what type it is.