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!
Multiplying large decimals rounds to ten decimals. Why?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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