Page 2 of 2
Posted: Tue Jan 16, 2007 11:34 am
by 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.
Posted: Tue Jan 16, 2007 11:49 am
by 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]
Posted: Tue Jan 16, 2007 12:40 pm
by 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.
Posted: Tue Jan 16, 2007 1:10 pm
by 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
Posted: Tue Jan 16, 2007 1:18 pm
by 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
Posted: Tue Jan 16, 2007 1:50 pm
by 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
Posted: Tue Jan 16, 2007 1:55 pm
by DSguru2B
Thank Ken, really. He pointed out the NULL factor and that gave us a lead. Great, now you can mark it as resolved.