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