Multiplying large decimals rounds to ten decimals. Why?

[djwagner]

Hello,

I have created a dummy job to test and demonstrate the following behavior (with no other logic to complicate things) and am hoping someone can explain the behavior that I am experiencing.

I have two Decimal(16,15) values coming in from a text file that I need to multiply together, and need a full Decimal(16,15) value output. (We are a financial organization, so the highest accuracy is important, which is why we need the full 15 decimal places.)



In the transform, if I perform any type of multiplication against the 15-decimal number, the result is automatically rounded to 10 decimal places. WHY? This occurs when multiplying the two 15-decimal numbers together or even when just multiplying the 15-decimal number by 1.

For example:

0.888888888888888 * 0.222222222222222 = 0.197530864200000 <---It is automatically rounded to 10...Should come out to be 0.197530864197530
or
0.888888888888888 * 1 = 0.888888888900000 <---Should come out to be 0.888888888888888




The only way that i have found it to work is to convert both values to a DFloat, performing the multiplication, and then convert back to decimal.

Can someone tell me why it works this way?


***** SAMPLE formulas and Output: *****

Input Data:

Field1=0.888888888888888 Data type is: Decimal(16,15)
Field2=0.222222222222222 Data type is: Decimal(16,15)

Output field is also Decimal(16,15)



DecimalToDFloat(Input.Field1,"fix_zero") * Input.Field2
= 0.197530864200000
DecimalToDFloat(Input.Field1,"fix_zero") * DecimalToDFloat(Input.Field2,"fix_zero")
= 0.197530864200000
DFloatToDecimal(DecimalToDFloat(Input.Field1,"fix_zero") * DecimalToDFloat(Input.Field2,"fix_zero"))
= 0.197530864197530 *****WORKS******
DFloatToDecimal(DecimalToDFloat(Input.Field1,"fix_zero")) * DFloatToDecimal(DecimalToDFloat(Input.Field2,"fix_zero"))
= 0.197530864200000

Input.Field1 * 1
= 0.888888888900000
DecimalToDFloat(Input.Field1,"fix_zero") * 1
= 0.888888888900000
DecimalToDFloat(Input.Field1,"fix_zero") * DecimalToDFloat(1,"fix_zero")
= 0.888888888900000
DFloatToDecimal(DecimalToDFloat(Input.Field1,"fix_zero") * DecimalToDFloat(1,"fix_zero"))
= 0.888888888888888 *****WORKS******
DFloatToDecimal(DecimalToDFloat(Input.Field1,"fix_zero")) * DFloatToDecimal(DecimalToDFloat(1,"fix_zero"))
= 0.888888888900000



Thank you!

[ray.wurlod]

What (environment variable) settings do you have to govern precision of intermediate results in decimal calculations?

[djwagner]

Thank you, thank you, thank you! That little nudge in the right direction was all that I needed.

I didn't know that there were environment variables in Admin that affected data sizes. I looked and saw the following environment variable:

APT_DECIMAL_INTERM_SCALE Default scale for decimal intermediate variables

It was in fact set to 10 (default). I changed it to 15 and it fixed the issue!

Thanks,
David