Subject 回复: Re: [firebird-support] Re: About UDF's problem
Author Yang Jin
Thank you for your detail explain.
But,don't bore.I still have some problem to ask.
The sentence 'Casting one scaled number to another of
different scale and precision is not a rounding operation, but a shifting of scale.' means shifiting = cutting?
I am not understand that mean because I found:
Field1, DataType:numeric(18,9),Value:13.283123456
cast(Field1 as numeric(18,2)) = 13.28;it can been thought as cutting.
Field2, DataType:numeric(18,9),Value:13.275000000
cast(Field2 as numeric(18,2)) = 13.28;
it is not cut,it still round.
So what's the 'shifting' means?

The source of problem is I want to sum a Field(numeric(18,9),Computed Field).I need first cast it to numeric(18,2), then sum them.
A record's value is 13.275000000(display in IBExpert),but the cast result still 13.27,not like the result I cast Field2 above.
Maybe it is the key of the problem.

Thanks again.


Helen Borrie <helebor@...> 写道:

Aha, casting a scaled decimal number to a smaller scale is different from
casting a floating point number to a scaled number. Scaled numbers are
stored as integers of the declared precision. The scale is stored in
another field as a power of 10. Casting one scaled number to another of
different scale and precision is not a rounding operation, but a shifting
of scale.

Your example was taking a numeric(18,9) value and scaling it down. My
example took a floating point number and rounded it. Different data types,
different arithmetic rules.

Actually, it is quite important with scaled numbers to make sure you don't
store more scale than you actually need. This is particularly so if you
follow the recommended practice and use scaled numbers for money
values. This practice is usually right, since it ensures exactness: zero
is always exactly zero; as long as you choose a scale of 2 to store
values, the value of a 5c coin is stored as exactly .05 of a dollar and a
dollar, in turn, is stored as exactly 1.00 dollars. You will bump into
problems if you store money values in a numeric(18,9) as a matter of
course, for no particular reason.

When you multiply or divide one scaled number by another, exactness is
maintained but you lose precision. A numeric(18,2) multiplied by a
numeric(18,2) produces a numeric(18,4) as a result. In systems where a
unit of currency represents a very small amount - such as Japan - it is
very easy to run out of precision.

So - the choice of floating point numbers vs scaled numerics is necessarily
a trade-off between accuracy and precision and a subject for careful study
of requirements.



[Non-text portions of this message have been removed]