Re: Multi-Currency in RDBMS: Integer, Decimal, Float.

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Wed, 08 Dec 2004 07:16:52 GMT
Message-ID: <ETxtd.4916$yr1.1929_at_newsread3.news.pas.earthlink.net>


LineVoltageHalogen wrote:

> Jonathan, the documet to which I refered can be found at the link you
> provided, particularly:
> http://ww2.hursley.ibm.com/decimal/decifaq1.html.

http://www2.hursley.ibm.com/decimal/decifaq1.html

3 w's and a 2.

 > When you say
> floating point decimal what do you mean? I was under the impression
> that the decimal type and float type are different? 1/10 = .10 in
> decimal world, 1/10 = .099999 in float point world, I could be wrong
> but I believe this is how things work within both Oracle and MSSQL.

Fortunately, there are other databases than those two :-)

In Informix, all DECIMAL types (including MONEY and NUMERIC, as opposed to FLOAT, SMALLFLOAT, REAL, DOUBLE PRECISION types) are stored in the same way. They are stored using a base-100 floating point representation. There is a sign/exponent byte that encodes the sign of the number and the sign and magnitude of the power of 100 to which the mantissa must be raised. The remaining bytes - a configurable number of them - encode pairs of decimal digits 00..99. There are a number of interesting details, but the type can store values in the

range 1E-126 to 9.9999999999999999999999999999999E+126 (plus zero, of 
course). There are supposed to 32 9's in that string. There are different figures for the range of the decimal exponent - but the difference is not usually material.

The type occupies from 2 to 17 bytes on disk. It can also be constrained such that the value has N digits after the decimal point, for N in the range 0 to 32. The number of digits before the decimal point is also fixed; it could be from 0 to 32 digits too, but the total length cannot be more than 32 decimal digits (16 base-100 digits).

> Floating point is an approximation and decimal is exact. I really
> appreciate your taking the time to answer my post, what you have
> provided has been a huge help with regards to resources.

Glad to have been of help.

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Received on Wed Dec 08 2004 - 08:16:52 CET

Original text of this message