Aggregator and sum function

[kcbland]

No. DataStage doesn't care what value you ASSIGN to a variable. It only matters when you use it, the value needs to be able to cast or promote according to how you want to use it.

[mattias.klint]

Not sure I got that. No one in my office can solve this problem. It sucks!

What is the best way to change the data type? How do I change it to char.

Thank you all for all your help, I really apprieciate it.

//Mattias

[quote="kcbland"]No. DataStage doesn't care what value you ASSIGN to a variable. It only matters when you use it, the value needs to be able to cast or promote according to how you want to use it.[/quote]

[DSguru2B]

I thought it was already coming in as char and you wanted decimal(x,4) where x is the length.
Lets start from the begining, this is going no-where.
-Handle nulls before doing any manipulations using
-Change it to the required format before doing aggregations.

Code: Select all

If ISNULL(in.Col) OR Len(Trim(in.Col)) < 1 then 0.0000 else FMT(in.Col,"R4")

Also post your stage variables the way you have defined it, including its initial values.

[mattias.klint]

Ok, you are right, thanks for listening to me. Here we go again:

Two feeds and one transformer and one aggregator.

Feed1:
--> all char (char comes into the job)
--> Hashed -->decimal, 4 (hased output is decimal)
--> Aggregator, sums two fields, groups on 4 (Decimal, 4 as input/output)
--> Hashed -->char (hashed output is char)

Feed2:
normal char

Transformer:
tryes to add Feed1_Field1 with Feed2_Field1

This works if done in a field, but not if I do it in the StageVar.

I have checked so there are no @NULL only 0.

Gracias,
Mat

[DSguru2B]

Apply the derivation i gave you in the previous post for both Feed1_Field1 and Feed2_Field1.
Your hashed file feed will be null if the lookup is not found. In your stage variable apply the following derivation

Code: Select all

If reflink.NOTFOUND then 0.0000 + Feed2.Field1 else reflink.Feed1_Field1 + in.Feed2_Field1

[mattias.klint]

THANK YOU! IT WORKS!!

It only took a day;-) The last piece of code did it. Perfect. I'm very VERY greatful.

Have a really nice day.

//Mattias

[DSguru2B]

Thank Ken, really. He pointed out the NULL factor and that gave us a lead. Great, now you can mark it as resolved.