| Subject | Re: [firebird-support] Re: About UDF's problem |
|---|---|
| Author | Helen Borrie |
| Post date | 2005-08-15T04:17:04Z |
At 11:07 AM 15/08/2005 +0800, Yang Jin wrote:
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.
./heLen
>So fast reply! Thank you very much.Aha, casting a scaled decimal number to a smaller scale is different from
>It's right.I had tried it after post the problem,had the same reuslt that
>you example.
>I can also understand your explain.But the field's datatype in Table is
>numeric(18,9),the Field value in Table is 13.275000000, of course display
>as 13.275 in 3dp.So I still not understand,
>why cast( 13.275000000 as numeric(18,2)) = 13.27,but
>cast( 13.275 as numeric(18,2)) = 13.28?
>Other info: the result of cast( 13.275000000 as numeric(18,1)) = 13.3, I
>can accept it.
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.
./heLen