Hello mr Worboys :)

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

I understood this point, and i don't think i got the question answered
yet. Let me start again from the beginning. I have a table with
(simplified example) two NUMERIC(18,4) fields, let's call 'em A and B.
...
create table MyTable (
pk bigint not null,
a numeric(18,4) not null,
b numeric(18,4) not null,
primary key (pk)
)
...

Later, to display the data, i'm using a query like this :
...
select pk, a, b, (a*b) from MyTable;
...

Again, i think i got the point that the result of (a*b) is bigger than
what numeric(18,4) can handle. I accept that. No problem. There's
nothing i can do about it. The issue here is for those numbers that
*should* fit in numeric(18,4), like case #2 above. (These are the
simplest of calculations, i'm sure there are people doing a lot hairy-er
calculations inside storedprocs, for example, and this should affect
those as well.)

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

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

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

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

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

My bad. I misread the sentence.


Regards,
t.s.