Subject | 回复： Re: [firebird-support] Re: About UDF's problem |
---|---|

Author | Yang Jin |

Post date | 2005-08-15T09:00:36Z |

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.

But

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.

regards

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.

./heLen

---------------------------------

DO YOU YAHOO!?

雅虎邮箱超强增值服务－2G超大空间、pop3收信、无限量邮件提醒

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

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.

But

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.

regards

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.

./heLen

---------------------------------

DO YOU YAHOO!?

雅虎邮箱超强增值服务－2G超大空间、pop3收信、无限量邮件提醒

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