Subject Re: [firebird-support] Numeric(18,4) calculations
Author Geoff Worboys
> So I could use

> declare variable
> LTemp double precision

> In place of

> LTemp numeric(18,2)

> (leave everything else exactly as is)
> and have the best of both worlds?

> Or will the final assignment still give me an overflow error?

With double precision data types you can safely (at least
within much wider limits) do whatever multiplication etc that
you want. But the answer will only remain accurate within 15
significant digits (total digits, both sides of the decimal

You may still get an overflow on the final assignment, but only
if the result is an actual overflow. Note that you may still
get other accuracy difficulties because double is only accurate
to 15 significant digits. Much remains dependent on the size
of the values you need to handle.

If the calculation required more than 15 digits for any interim
result then your final result (even if less than 15 digits) may
not be exact. You need to talk to an real expert in maths
(rather my limited knowledge) to understand exactly what level
of errors appear in such situations.

My preference for double comes about because;
- I already have the code in place for explicit rounding
- the values I handle fit within the 15 significant digits
(mostly, I think there may be some calculations where
issues could arise, but are quite unlikely in my app)
- I get to do rounding by my clients rules and not by
the rules imposed by Firebirds scaled integers
- double deals with significant digits rather than decimal
places which means the calculations deal with significant
digits rather than ever expanding decimal places.

There are compromises with either type, all you can do is the
best that you can with your choice. But for financial
applications (at least) it is important to understand just what
compromises you have made and what they mean to your results.

The simple truth is that Firebird does not really have a data
type large enough to cope with a broad range of currency types.
Its types are probably large enough for storage, but to
manipulate the values in script we really do need a larger
type. Of course it is not just Firebird, even if it had
support for larger types, your client software also needs to
support such types to make it useful. That is; the problem is
really an industry wide problem and not Firebird specific.

There are specialised solutions out there for C++ libraries,
but making such solutions available to applications is not
necessarily easy (or good for performance).

Geoff Worboys
Telesis Computing