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

Author | Helen Borrie |

Post date | 2006-05-24T13:58:02Z |

At 10:44 PM 24/05/2006, you wrote:

with a scale of 4. That will depend on the business requirements, so

find out what the customer needs.

However, when doing calculations, remember that the "scale" of a

scaled decimal number represents a power of 10. It's basic

arithmetic that when you multiply or divide numbers you add their

scales (exponents).

Hence, the following results - check it on your calculator until you

exhaust its capability.

1.23 * 4.56 = 5.6088

1.23 * 4.56 * 7.89 = 44.253432

1.23 * 4.56 * 7.89 * 1.23456789 = 54.6338661694.... (here my Casio

MX-12 calculator has run out of precision)

So adjust your multiplication and division expressions accordingly,

using CAST or a rounding algorithm, according again to business

requirements. If you don't understand the term "business

requirements", please ask again.

./heLen

> >> both "qty" and "unit_price" is numeric(18,4).No, that is not what he's saying.

> >>

> >> From my testing, the 'limit' seems to be around 11-12 digits.

> > There is nothing to wonder at: multiplying numeric(18,4) by

> > numeric(18,4) you are getting numeric(19,8).

>Understood. But is there a way to do the calculation ANYWAY since the

>resulting number *should* fit in numeric(18,4) ? Using numeric(18,4) as

>'currency' data type pretty much dictates that at some point it will be

>used in such calculations.

>

>Unless you're saying that we should not use numeric(18,4) as a currency

>data type at all...

>If that's the case, then what do you suggest we useFirst reconsider whether you really need to store currency values

>for this purpose?

with a scale of 4. That will depend on the business requirements, so

find out what the customer needs.

However, when doing calculations, remember that the "scale" of a

scaled decimal number represents a power of 10. It's basic

arithmetic that when you multiply or divide numbers you add their

scales (exponents).

Hence, the following results - check it on your calculator until you

exhaust its capability.

1.23 * 4.56 = 5.6088

1.23 * 4.56 * 7.89 = 44.253432

1.23 * 4.56 * 7.89 * 1.23456789 = 54.6338661694.... (here my Casio

MX-12 calculator has run out of precision)

So adjust your multiplication and division expressions accordingly,

using CAST or a rounding algorithm, according again to business

requirements. If you don't understand the term "business

requirements", please ask again.

./heLen