Subject | RE: [firebird-support] Numeric(18,4) calculations |
---|---|

Author | Maya Opperman |

Post date | 2006-05-25T07:22:40Z |

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

precision".

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

problems.

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:

select

(Qty * UnitPrice * 1.14) as ExtValueInclVAT

from MyTable

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

Declare variable LTemp: decimal(18,2)

..

for

select

Qty,

UnitPrice

from MyTable

into

OQty,

OUnitPrice

Do

Begin

LTemp = (:OQty * :OUnitPrice);

LTemp = LTemp * 1.14;

OExtPriceInclVAT = LTemp;

End;

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?

Maya

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

precision".

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

problems.

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:

select

(Qty * UnitPrice * 1.14) as ExtValueInclVAT

from MyTable

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

Declare variable LTemp: decimal(18,2)

..

for

select

Qty,

UnitPrice

from MyTable

into

OQty,

OUnitPrice

Do

Begin

LTemp = (:OQty * :OUnitPrice);

LTemp = LTemp * 1.14;

OExtPriceInclVAT = LTemp;

End;

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?

Maya