SAP HANA sql - Decimals truncated in division result

[Former Member]

Hello,
in division operation result, the decimals digits are truncated to 6, even if I set a scale of 10.

For example :
select to_decimal(1/14, 28,10) from dummy; --> it s truncated to 6 digits (0,071428)
select to_decimal(3.14159265358979,28,10) from dummy; --> it displays 10 decimals as required.

How can I have the 10 decimal digits from the division result?
Thank you

[Former Member · Accepted solution]

Hello, I found that the scale in a division result is given by this rule:

scale = max(6, NumeratorScale + DenominatorPrecision + 1)

In the above example:

select 1/to_decimal(14,28,10) from dummy;

Source:
https://help.sap.com/viewer/4fe29514fd584807ac9f2a04f6754767/2.0.04/en-US/20a380977519101494ceddd944...

[Jörg_Brandeis]

Hi Ilaria,

when you need more precision, you can change the denominator precision before the division:

select to_decimal(1) / to_decimal(6) from dummy;

Regards,
Jörg