0

Considering the taxable amount (rateable), the number of days and the rate, I must calculate the interest applied (with the compound rate formula) but I have a problem with floating-point and rounding applied by DB2 V12 (Z/OS).

(Note: The same statement on DB2 LUW does not give me problems)

The value of the interest that I expect is 17,84€ instead I get 17,86€

I'm using this statement:

SELECT CAST(CAST(RATEABLE AS DECFLOAT)
     * ( 1 - ( POWER ( (  1 + CAST(RATE AS DECFLOAT) / 100 ),
                       ( -1 * CAST(NUMBER_DAYS AS DECFLOAT) / CAST(DIVISOR AS DECFLOAT) )
                      )
              )
        ) AS DECIMAL(18, 2)
    ) AS PAYMENT_INTEREST
FROM  (
        --- I simulate accessing my DB2 table.
        SELECT CAST(92247.38 AS DECIMAL(18, 2))     AS RATEABLE,
               CAST(0.249000 AS DECIMAL(12, 6))     AS RATE,
               INTEGER(28)                          AS NUMBER_DAYS,
               INTEGER(360)                         AS DIVISOR
        FROM   SYSIBM.SYSDUMMY1
    )  AS TEMP

If I defined the "RATE" field as DEC (12,3) the calculation is correct but obviously I would not be able to manage more rates with more decimals.

Now, what am I missing here?

Thanks.

killer
  • 11
  • 6

1 Answers1

0

Don’t use float datatypes as they will give you this issue. Just define your fields as decimals with enough decimal points to hold the data that you need them to hold. You can always round them in your queries if you need show them with reduced precision

NickW
  • 8,430
  • 2
  • 6
  • 19
  • I'm not a DB2 guy, but as I understand it DECFLOAT is a precise data type. – jarlh Jun 20 '22 at 20:49
  • I purposely used DECFLOAT for more accurate calculations. Having more decimal places makes the calculations more accurate. The rounding I do only at the end, when I expose the data. But there is probably more that escapes me and that I am not understanding :) – killer Jun 20 '22 at 21:35
  • `DECFLOAT` is an 8-byte (or 16-byte) floating point value. It's not a precise data type. – The Impaler Jun 20 '22 at 23:18