>> Can you give an example statement which would not cause an overflow?
>> (I too have this problem, and I'm not quite sure which value to cast
>> where and when)
> select cast ((12.6789 * 67.5678) as numeric (18,4)) as result from rdb$database
> yields 856.6854 (856.68537942 as numeric(18,8)

I'm perfectly OK with this result.

> You could get an overflow on the left side of the decimal,
> though.

This is what my original message was about. Here's the snippets of the
message :
...
From my testing, the 'limit' seems to be around 11-12 digits.
So :
case 1 : '123,456,789' * '100' is OK.
case 2 : '123,456,789' * '1000' will bomb.
case 3 : '123,456,789' * '10000' will bomb.
...

There were no fractional parts involved in the calculation, and yet the
maximum scale i can achieve is 11-12 digits instead of the supposed 14.
Both numbers are fetched from a field/column with numeric(18,4) datatype.

> That is a trap with storing money as scale 4 numbers, esp.
> in currencies with low-value units. Precision blows out at 18,
> regardless of scale.

This is exactly my situation right now, as i have outlined (maybe a bit
unclearly) in my original post. I think i mentioned that the local
currency is *huge* compared to USD (9300Rp per 1USD, the last time i
checked), so we're talking about 3 or 4 digits difference in scale here
right off the bat. This also means that losing one cent USD is worth
almost 100Rp, this scares the hell out of me :).

I'm not sure what you meant by 'business requirements', but the database
(and application) is supposed to handle multiple currencies (which means
at least IDR and USD at this point).

The argument for using numeric(18,4) was because the need to support
USD. As far as i understand it, USD needs at least 2 digits on the right
of the decimal points (the cents), so the 'safest' way to ensure
accuracy is to use 2x as many digits. (Whatchacallit, Nyquist Rate?)

> For those currencies you'd need to be working
> with double precision at some point[s]. Have you read Geoff Worboys'
> article 'Working with Currency and Scaled Numeric Data Types' at
> www.ibobjects.com/TechInfo.html ?

Thank you for the pointers. This is the 'conclusion' part at the end of
that document:
....
It is not essential to use numeric data types (although when the is
scale is 0 or the values stored are small you are reasonably safe).
Instead, you can implement your monetary values using double
precision. This has a significance of only 15, but that is often
sufficient.
...
If i understand that paragraph correctly, using numeric data types are
only recommended (or "safe") if the values are small. Exactly my case
until recently, when the database has grown 'big' enough to accumulate
some values.

The second recommendation is to use double precision type (with the
caveat of having only 15 digits). Given that numeric(18,4) only gives me
14 digits, i think this is a good deal :). Please tell me whether this
is a good idea before i go ahead and spend a couple of days updating the
production database.... :)

The next paragraph in that document is rather ominous though...
...
However, if you use double precision you will probably need to
implement a UDF library containing rounding functions, so that you can
set up your triggers and stored procedures to force the stored value
to be rounded as required.
...
I have a hunch that a couple of weeks from now, i'm gonna give the nice
people here more trouble because i fail to understand what "to be
rounded as required" *really* means :)

Thank you all in advance,
regards,
t.s.