Decimal rounding issue

[former_member318010]

Hi,

I am facing rounding issues in decimal calculation. In my model I have 3 fields.

AmountinEUR    Decimal (13,2)          = 33698.12

Rate                   Decimal (9,5)          = 1/0.77275

Result               Decimal (13,2)          = wrong value 43,608.12 expected value 43,608.13

Calculation is very simple, Result = Amount in EUR * Rate

When I do calculations with all of the above variables as Double type then I get below Value

43,608.12828109429

But when I use Result as Decimal (13,2), Value is getting calculated as 43,608.12

Has anyone face this issue? Looks like its truncating when calculating decimal but not rounding it.

We have HANA on SPS69. Are there any notes around this?

Thanks,

Nitesh

[former_member182302 · Accepted solution]

did you try using round() function?

Regards,

Krishna Tangudu

[former_member318010 · Accepted solution]

Hi,

Round() does work when I first calculate everything in Double and then use Round(Double,2).

But if I directly pass Decimal = Double instead of Round(Double,2) then I dont get any rounding.

All my measure are in decimal format in database table. I have to convert to Double do calculation and then use round to get desired results. But then wont it be CPU intensive operation which should not be necessary in first place.

I am just trying to figure if this how I am suppose to do all of the calculation or is this bug only in my software version. If its there only in SPS69 and if not observed on SPS70 or higher then I will get my HANA version upgraded.

Thanks,

Nitesh

[former_member318010 · Accepted solution]

I found this note:

1895981 - to_decimal no longer provides rounding

*************************************************************************

In SP06:

select to_decimal(1.249,3,2) from dummy

returns 1.24

In SP05 and eariler:

select to_decimal(1.249,3,2) from dummy

returns 1.25

Reason and Prerequisites

In SP06, to_decimal no longer provide rounding implicitly.
This is by design

Solution

User can nest round function explicitly.

eg:

select to_decimal(roun(1.249,2),3,2) from dummy

This will return 1.25 as before.

**************************************************************************

Not sure if I am suppose to use round function. is is going to change in SPS7 or future releases?

Thanks,

Nitesh

[former_member182302 · Accepted solution]

Hi Nitesh,

I don think they will change it. The way now to_decimal works is similar to CAST. Just truncating till the required places. This functionality is also required when the user wants to just truncate but not to round off.

On the other hand if user still wants it to be rounded , he can use round().

So hence giving both the functionalities. So i'd guess that this behavior wont change.

Regards,

Krishna Tangudu