Rounding Issue

jagadam · Post by **jagadam** » Mon Oct 11, 2010 1:17 pm

I Have an issue here . My values I want to have like this

10690164.245 as 10690164.25
-2374823.085 as 2374823.09

But When I do the below options I am getting results not as expected
Doing a DecimalToDecimal(Column," ").

Any Suggestions is appreciated

round_inf

10690164.245 10690164.24
-2374823.085 2374823.09

Ceil

10690164.245 10690164.25
-2374823.085 2374823.08

floor

10690164.245 10690164.24
-2374823.085 2374823.09

ray.wurlod · Post by **ray.wurlod** » Mon Oct 11, 2010 1:47 pm

floor and ceil are giving correct results (assuming you're applying that before removing the sign).

From memory, the behaviour of rounding is determined additionally by whether the preceding digit is odd or even when the least significant digit is 5. Try it with 10690164.345.

jagadam · Post by **jagadam** » Mon Oct 11, 2010 2:07 pm

Hi Ray,
U might be right on the even , odd digit on the preceeding digit.But I need ouput of 10690164.245 as 10690164.25 As in oracle rouding is getting to 10690164.245 as 10690164.25 not in datastage .My Unit testing is failing because of this.Is there anything we can do for this

In Datastage It is doing Round half to odd for Tie breaking
In Oracle It was doing Round half away from zero.

How can we get the desired results in Datastage ?

jagadam · Post by **jagadam** » Tue Oct 12, 2010 6:07 am

Any Suggestions on this please let me know.Is this a bug on Datastage

ArndW · Post by **ArndW** » Tue Oct 12, 2010 6:45 am

I don't see a bug or a problem here.

Rounding "10690164.245" to 2 decimal places gives "10690164.25" using nearest value.

You could force matters by doing a trunc_zero after adding 0.005 to positive numbers or subtracting 0.005 from negative ones.

jagadam · Post by **jagadam** » Tue Oct 12, 2010 7:30 am

Hi Andrw,
If that the case then If I have a value of 10690164.244 instead then I will have 10690164.244 +.005 =10690164.249 ==> 10690164.25 .Which is not I wanted ...

DS Rounding mechanism is using Round half to odd for Tie breaking
This is the issue I am talking about adding .005 affects other records which are not tie breaking

Let me know If I am missing anything in here

vivekgadwal · Post by **vivekgadwal** » Tue Oct 12, 2010 7:44 am

In my experience with this I have learnt that there are many ways a rounding function can be implemented, like Accounting etc. DataStage does it this way. Other tools might use a different algorithm. ArndW is right, there is no bug with this.