Hi t.s.

> 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.

And because the values are defined as NUMERIC(18,4) the actual
calculations become:
case 1 : '123,456,789.0000' * '100.0000' is OK.
case 2 : '123,456,789.0000' * '1000.0000' will bomb.
case 3 : '123,456,789.0000' * '10000.0000' will bomb.

The result of each of these will be a value that needs 8
decimal places. Since the largest numeric available with FB
is NUMERIC(18, *) the result becomes NUMERIC(18,8) - and hence
you only have 10 digits to the right of the decimal. (Actually
11 digits but not in the full range.)

> 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?)

The thing to watch here is... What is going to happen at the
client? What data type are you going to use. It is one thing
to choose an appropriate storage value, but that is only useful
if the client can represent it (and manipulate it) without loss
of precision (or type).

> 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.

I guess the point is more that you can make various assumptions
while your data is relatively small. When you begin to hit
the extremes additional precautions must be taken.

You need to understand that NUMERIC data types are scaled
integer data types, the implementation is a strange mix of
integer and floating point rules - presumably based on the SQL
standards but I dont know for sure. If your data starts to get
large you will have to take some additional precautions in
regard to casting interim responses back to the necessary data
types, and perhaps even doing "interesting" things with your
calculations (such as purposely dropping or rounding some of
the decimal places) to avoid overflow during interim values in
calculations.

> 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 :).

No, you misunderstand. NUMERIC(18,4) gives 18 digits of
precision - but has a fixed decimal place - compared to
double's 15 bits of precision - but has a floating point.

It is true that, while ever your data has no decimal places,
the difference becomes 15 to 14. But the accuracy of your
results are still limited to 15 digits (decimal or otherwise).
If you really have no decimal places then BIGINT (or
NUMERIC(18,0)) may be the better option as then you have a
full 18 digits of precision to play with.

> Please tell me whether this is a good idea before i go ahead
> and spend a couple of days updating the production database
> .... :)

Give yourself more than a couple of days, this stuff gets
REALLY hairy when you start to hit the extremes.

> 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 :)

I am hoping that you already have some idea of what rounding
is required on your currency values. It is common for
financial systems to use statistical rounding (sometimes called
bankers rounding), but some situations may have quite specific
rounding requirements. For example the tax office in Australia
used use some weird stuff but has now reverted to either
truncating cents or standard (0.5 goes up) rounding.

You must also take care that you round at appropriate times.
Many storage fields should probably carry values rounded to the
cent (not expected to carry additional precision), other fields
may need to carry the extra precision. Similarly in your
calculations you must find out at what point rounding is
required (or appropriate). If you are starting to hit the
limits of NUMERIC or double precision then you may be truly
constrained as to the final accuracy - unless you find some way
to implement your own currency data type in the database.

I have a payroll application that still uses double (was built
before IB/FB supported int64 beneath their large NUMERICs).
But this application takes much care to perform explicit
rounding at (hopefully) appropriate places. Even with such
precautions you can end up with curious results if you perform
something like:
SELECT SUM(AFIELD) FROM ...
over a very large number of rows (the lack of exactness in the
double type can lead to a cent or two either way in the result,
although so far my explicit rounding has minimised the impact
of this issue).

I am afraid that many programmers do indeed implement financial
systems without fully understanding the issues resolved. You
do need to be very careful, and usually quite explicit, if you
want the system to respond as needed by your clients.

--
Geoff Worboys
Telesis Computing