Skip to Content
author's profile photo Former Member
Former Member

Quirk with Arithmetic MUL function


In my source file I've got 2 fields (sf1, sf2) declared as type xsd:string. My requirement is to multiply those two fields and map the value to my destination field (df1) which is also of type xsd:string.

What I've noticed is if I have, for instance, sf1=3427.00 and sf2=97.0 then df1 is equal 332419.000 (we also send the result through a FormatNumber routine to get the 3 decimal places). In this scenario we see no issue.

The problem appears when we use larger numbers. An example we've seen is if sf1=41041.00 and sf2=817.0. The answer SHOULD be 33530497.000. What it actually does is calculate the value as 3.3530496E7. When that gets mapped to df1 it ends up getting set to 33530496.000 which is off by 1. We have other examples where the value is off by a little more than 1.

My question are:

1) Can anyone explain to me why Java/XI calculates this way (if it's not a bug) and,

2) does anyone have an idea as to how I would resolve this issue?

We are on SP15.


Add comment
10|10000 characters needed characters exceeded

1 Answer

  • Best Answer
    Posted on May 30, 2007 at 02:45 PM

    <i>Function Category: Arithmetic

    You can only enter numerical values for this category (this includes values with digits after the decimal point). If the value cannot be interpreted as a digit, then the system triggers a Java exception. Otherwise, all calculations are executed with the precision of the Java data type float. The format of the result value depends on the result:

    &#9679; If the result is a value with digits after the decimal point, these remain unchanged.

    &#9679; Exception: If a zero follows the decimal point, then this is cut off. This means that the result of the calculation 4.2 – 0.2 is 4 and not 4.0.

    Also note that Java values of type float are converted to the second system before the calculation. The result is calculated there and then this result is converted back to decimal format. The conversion may result in positions after the decimal point that are periodically repeated and then automatically cut off. In the decimal system, this can result in inaccurate results (example: 2.11 + 22.11 = 24.220001). If the number of positions after the decimal point is to be restricted to two, for example, in the case of values for a currency, you can format the values after the calculation by using the standard function FormatNum.</i>

    an extract from -

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Stefan Grube

      Its my fault. I have not seen the SP level.

      Jones you can write the UDF as mentioned in the blog. Then it will take care of your issue. Thanks for correcting me Stefan.