Rounding off issue with datastage

[prasson_ibm]

Hi,
I am facing rounding off issue in datastage.In my job Source and Target are oracle. Source datatye is Float and target it is Decimal(14,2).

In target table data is getting rounded for ex:-

Source Selling value
134.64912280701754385964912280701754386
Target Selling value
134.65

But my expected output is 134.64.

I tried to apply below function in transformer
DecimaltoDecimal(Selling Value,'floor')

In this case my output is :-

Source Selling value
134.64912280701754385964912280701754386
98.24000000000000000000000000000000000026
Target Selling value
134.64
98.23


But our expected output is :-
134.64
98.24


Can anyone suggest us to achieve this result?

[bob7027]

try this;
DecimalToDecimal(mylink.mydec,"trunc_zero")

or

DecimalToDecimal(mylink.mydec,"round_inf")

keep the target decimal length of 10 and a scale of 2.

[prasson_ibm]

Hi Bob,
No Luck,with first function but second one is almost correct,but for some folting number its not working properly

With Function DecimalToDecimal(mylink.mydec,"trunc_zero")
Input
142.543859649122807017543859649122807018
4278.02631578947368421052631578947368421
212.157894736842105263157894736842105264
334.900000000000000000000000000000000001
409.750000000000000000000000000000000001
Output
142.54
4278.02
212.15
334.89
409.75

For 334.900000000000000000000000000000000001 i am getting output as 334.89 but i am expecting 334.90

[prasson_ibm]

Hi,

Can someone help me with above issue?

[madhusds]

Just change the precision to 2 and try......

[prasson_ibm]

Hi,

My input is float and target is decimal(14,2) which i cant change it.

[jerome_rajan]

This solution may not be optimal but will serve as a work-around while you search a better solution

Code: Select all

Left(col,Index(DecimalToString(col),".",1)+2)

I haven't actually implemented it. Do let me know if it works.

Note: You might want to add a StringToDecimal just in case

[prasson_ibm]

Hi Ranjan,
Thanks for your reply but it seems its not working.

Input
212.157894736842105263157894736842105264

Output
212.16

[jerome_rajan]

Ok, this should work

Code: Select all

Left(DecimalToString(col),Index(DecimalToString(col),".",1)+2)

[prasson_ibm]

Hi,
Thank you very much its working....
But i have around 8-10 coumnls like this,will it be good approach to convert it like this?
Anyways for time being its fine for me.

[jerome_rajan]

Can't think of anything other than replicating the same logic.

[priyadarshikunal]

You can try derivation substitution in transformer.

The conversion using trunc_zero may not always work as it depends on how many decimal points the database and datastage supports for float. For example oracle supports many decimal points more that float in datastage. You many need to change the datatype in datastage to double to make it more accurate. However if the solution provided earlier works for you then no point in changing the same.

[sudha03_vpr]

Source columns of Datatype Decimal(26,11) get converted accurately into Decimal(18,3) & Decimal(18,8) & Decimal(18,7) into three cols seperately. You may need to use
DecimalToDecimal(<col_name>,trunc_zero)
and assign it to the target column length.