Subject | Re: [firebird-support] Numeric(18,4) calculations |
---|---|

Author | t.s. |

Post date | 2006-05-25T05:58:20Z |

Hello mr Worboys :)

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

why it's even more important to get right on the server side...

Regards,

t.s.

> And because the values are defined as NUMERIC(18,4) the actualI understood this point, and i don't think i got the question answered

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

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 theI don't do data manipulation (calculations, etc) on the client, which is

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

why it's even more important to get right on the server side...

>> The second recommendation is to use double precision typeMy bad. I misread the sentence.

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

Regards,

t.s.