Subject RE: [firebird-support] Numeric(18,4) calculations
Author Maya Opperman
Hi All,

I am having a serious case of deja vu here, only instead of Delphi and
the "real" type vs the "currency" type, it's "numeric" vs "double

The reason why I steered clear of double precision in Firebird is
because of all the hassles I had with real types in Delphi.

You may not have come across the problem just yet, but you will at some
stage have some weird rounding issues with "real"/"double precision"
types because of what they represent, namely an exponent.

A while back I had an issue with my application rounding incorrectly.
For eg. 4.585 was rounding to 4.58, not 4.59 even though the same
function was rounding up correctly for other numbers. The reason being
that the number was actually 4.5849999999999999999999999999999999999....
(Even Delphi's debugger displayed the number as 4.585)
The solution was to rewrite the Delphi Round function which accepted and
returned "currency" values instead of "real" values, and voila, no more

From then on, I learned if you want accuracy use a type which is
actually an integer type behind the scenes, which is why I chose the
numeric over the double precision type.

The only issue I have now in Firebird is the overflow problems.

I have managed to reduce them a bit as follows: (but this is very long
winded, and I was wondering if there wasn't a more elegant solution)

In place of:

(Qty * UnitPrice * 1.14) as ExtValueInclVAT
from MyTable

(luckily it's a stored procedure) I now do this

Declare variable LTemp: decimal(18,2)
from MyTable
LTemp = (:OQty * :OUnitPrice);
LTemp = LTemp * 1.14;
OExtPriceInclVAT = LTemp;

Ie. I do not more than one multiplication per line.

Is there another simpler way of telling firebird that you don't care
bout those extra decimal places, and would rather make use of the
"space" on the left?