> So, to rephrase my question, how do one go about doing these
> calculations "safely" then ? Because this doesn't work :
> ...
> select cast((a*b) as numeric(18,4)) from myTable;
> ...

> Downcasting individual terms works somewhat, but we lose
> accuracy by deliberately cutting down the fractions, and
> tedious to write:
> ...
> select (cast (a as numeric(18,2)) * cast (b as numeric(18,2)))
> from myTable;
> ...

The importance of this second example is that it shows
explicitly what decisions you make to fit within the available
data types. Only you can make those decisions, Firebird cannot
guess for you which fields should lose precision before
executing the operation.

> Someone on the list once posted a nice shorthand trick to do
> this (sorry, i don't remember who to credit for this...):
> ...
> select cast((1e0 * a * b) as numeric(18,4))
> from myTable;
> ...
> Seems that it works by forcing the calculations to be done in
> double precision and then downcast-ed to numeric(18,4). As
> far as i can say, it works for case 2 and case 3 in the
> example above, but i'm not sure what is being sacrificed
> here...

And you are right to be cautious. The trick turns it into
double that has only 15 digits of significance. So you may
avoid the exception but you may also lose accuracy. See my
notes about compromise on my previous posting (in reply to
someone else on this thread).

> I don't do data manipulation (calculations, etc) on the
> client, which is why it's even more important to get right
> on the server side...

Do you have the client take the value and display it? That
in itself is a manipulation. If a client was to take a
NUMERIC(18,4) and display it using double as an interim value
then it could be misrepresenting your value if it has more
than 15 digits. Hence my reminder that client support for
these data types is important. (Hopefully your client is not
doing that.)


I regret to say that I dont have good solutions for these
questions. I can only say that I have looked and so far only
managed to find what works for my limited situation, which
happens to fit (well enough) within 15 digit significance of
double precision.

For other requirements, and I fully acknowledge that there are
applications out there for which 15 digits is just not enough,
I am really not sure what to suggest. The options seem to be:

. Build your own data type support, perhaps using something
like MAPM http://www.tc.umn.edu/~ringx004/mapm-main.html
or more probably a scaled 128bit integer - for which I do
not have an available link
OR
. Write your manipulations carefully, dropping precision
whereever you can get away with it - or performing some
sort of clever "carry over" manipulations when overflows
are detected during interim calculations.

As per my previous email, this is not a Firebird specific
problem. Firebird, as far as I am aware, is behaving as per
the SQL standard in this regard. It is an industry wide issue
that I suspect many people are waiting to see resolved.

--
Geoff Worboys
Telesis Computing