Subject | Re: [firebird-support] Overflow on multiplying decimals |
---|---|

Author | Ivan Prenosil |

Post date | 2010-07-28T15:01:49Z |

> Is there an way of preventing an overflow error no the following stored procedure calculation:I showed the solution on FB Conference 2006, you can obtain correct result by using this "formula"

>

>

> 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 tried

>

> OExtendedPrice = cast((IPrice * IQty) as decimal(18,2));

>

> but it still gives an error.

>

> I know it gives an error because the result is 403,925,005,217,813.00000 before being passed to 403,925,005,217,813.00, but is

> there any way I can tell it to drop the last 3 decimal places up front?

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))

Ivan

http://www.volny.cz/iprenosil/interbase/