Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
Note: This blog is the repost of an old blog on an old profile as part of the process of pulling all my content together on a consolidated profile.

This is going to be a short blog but will hopefully highlight accuracy problems that might be occurring if you don’t explicitly round your formula results before:

  • explicitly casting them to decimal using to_decimal, or

  • returning them as decimal from a procedure or table function i.e implicit casting based on definition of return table/fields


I picked this up whilst doing a very detailed test script and noticing that I was losing cents everywhere, after a bit of investigation I realised a truncate was being performed instead of proper rounding. Small differences on each amount but a lot of small differences can in the end make a huge difference.

Note: TO_DECIMAL used to provide implicit rounding but this behaviour changed from SP06 onwards, please refer to SAP Note 1895981 – to_decimal no longer provides rounding.

Here is my sample code you can paste into SQL console to play with the behaviour:
DO BEGIN

DECLARE dec_value DECIMAL(23,2) := 10407.06;

-- Take dec_value and scale by 1000
SELECT dec_value/1000 AS "ResultNotRounded" FROM dummy;

-- Then cast it to DECIMAL(23,2) to simulate the cast that happens when returned by table function
SELECT to_decimal(dec_value/1000,23,2) AS "ResultCastToDecimal" FROM dummy;

-- To get the properly rounded answer, round first then cast
SELECT to_decimal(round(dec_value/1000,2),23,2) AS "ResultRoundThenCast" FROM dummy;

END;

Here we have the result of the formula to 5 decimal places:



Here is the result of the formula cast to 2 decimal places, you can see here it that it simply performed a truncate instead of rounding to 2 decimal places:



Here you can see that rounding it to two decimal places and then casting preserves the correct rounded value:



Hope this small tip helps someone achieve more accurate results. Have a great day!
Labels in this area