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

> >> both "qty" and "unit_price" is numeric(18,4).
> >>
> >> 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...

No, that is not what he's saying.

>If that's the case, then what do you suggest we use
>for this purpose?

First reconsider whether you really need to store currency values
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