Subject RE: [firebird-support] Overflow on multiplying decimals
Author Maya Opperman
>> 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));

>You have to cast the individual fields so the intermediate
>result doesn't overflow.

The thing is, I don't want to truncate the individual values, I only want to truncate the result.

I ended up doing this:

if (div(IQty, 1000) = 0) then
OExtendedPrice = (IPrice * cast(IQty as decimal(18,0));
OExtendedPrice = (IPrice * IQty);

which got me out the pickle for that particular transaction that the customer had posted, because they had no decimals in the quantity of that transaction, but I'm sure it'll be back to the drawing board sometime soon, when a fraction is used as well...

I guess what I'm asking is impossible though, because the database engine has to put the result somewhere, before it truncates it, and it too will have a limitation to the size of its own variables ;-(