RE: [firebird-support] Overflow on multiplying decimals

Author

Maya Opperman

Post date

2010-07-28T15:19:31Z

>> Is there an way of preventing an overflow error no the following stored procedure calculation:
>> OExtendedPrice = cast((IPrice * IQty) as decimal(18,2));

>> IQTY decimal(18,3) = 100.000
>> IPRICE decimal(18,2) = 4,039,250,052,178.13
>> OEXTENDEDPRICE decimal(18,2) will be 403,925,005,217,813.00 (still within 18 significant digits, 17 actually)

>I showed the solution on FB Conference 2006, you can obtain correct result by using this >"formula"

> A × (B+C) = A × B + A × C

>i.e. split one of the values into integer and fraction part, and cast intermediate >NUMERIC(x,5) to (x,2), e.g.

> IPrice * CAST(IQty AS BIGINT) + CAST(IPrice * (IQty - CAST(IQty AS BIGINT)) AS >NUMERIC(18,2))