Rounding bug in Query when doing aggregate function?

[tjandra_afandi2]

Hi, I have this query:

SELECT T1.[ItemCode], T1.[ItemName], T1.[AvgPrice], SUM(T0.[InQty]-T0.[OutQty]) as 'NetQty', SUM(T2.[TransValue]) as 'Amount'

FROM OIVL T0  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

INNER JOIN IVL1 T2 ON T0.[TransSeq] = T2.[TransSeq]

WHERE (T0.[DocDate] >= '[%1]'/*i:FromDate*/ OR '[%1]'/*i:FromDate*/ ='') AND (T0.[DocDate] <= '[%2]'/*i:ToDate*/ OR '[%2]' = '')

GROUP BY T1.[ItemCode], T1.[ItemName],T1.[AvgPrice] order by T1.itemcode

If I run this, the "Amount" field is rounded (ie. no decimal points). I know it because I compare it with running it directly on SQL Management Studio.

The rounding doesn't happen if I don't use SUM and GROUP BY.

Is this a bug? If not, how to solve/change the setting?

Thanks

[kothandaraman_nagarajan]

Hi,

Please check this thread https://scn.sap.com/thread/616108

Thanks

[Former Member]

Hi Tjandra,

You can try using CONVERT function on SUM aggregate in SQL. You can set the decimal places as per your requirement. Hope this helps.

With Regard

Shiva

[kothandaraman_nagarajan]

Hi,

This is not bug. For more information, please refer thread Definitions of Decimal Place Settings | SCN

Thanks

[tjandra_afandi2]

Hi Nagarajan, thanks for that link.

I've read that, but it doesn't answer my question. FYI in SAP the setting is set to 2 decimal points.

For example: if I use this below, it will give me the correct TransValue (without rounding):

SELECT TransValue

FROM IVL1

WHERE TransSeq <= 5

36,636.72

44,833.53

10,797.64

8,075.70

105,162.75

But if for example I do SUM on TransValue, it will round the resulting number:

SELECT SUM(TransValue)

FROM IVL1

WHERE TransSeq <= 5

205,506 (instead of 205,506.34)

Interestingly, looks like if I do any operation on it, it will always round the resulting numbers:

SELECT TransValue + 0

FROM IVL1

WHERE TransSeq <= 5

36,637

44,834

10,798

8,076

105,163

So is that "by design" that SAP will do rounding for any math expression?

Thanks