I have witnessed some strange behaviour (what I think is strange!) with the NUMBER datatype. I have a column which is defined as NUMBER - which means it is a floating point number eg. the QTY_CHARGED column in the following table ...
SQL> desc eb_inv_item
Name
Null? Type
----------------------------------------- -------- ----------------------------
E_INVNUM
NOT NULL NUMBER(10)
INVITEMNUM
NOT NULL NUMBER(10)
INV_ITEM_TARIFF
NOT NULL NUMBER(5)
INV_ITEM_TYPE
NOT NULL CHAR(1)
INV_ITEM_CODE
NOT NULL NUMBER(5)
RATE_CHARGED
NOT NULL NUMBER
QTY_CHARGED
NOT NULL NUMBER
AMT_INV_ITEM
NOT NULL NUMBER(11,2)
DATE_INV_ITEM
NOT NULL DATE
CHARGE_SOURCE
CHAR(1)
INSTALLNUM
NUMBER(10)
When I select distinct values for the qty_charged column I get ...
SQL> select distinct QTY_CHARGED from eb_inv_item where QTY_CHARGED < 5;
QTY_CHARGED
-----------
0
.0
.032967035
.03333
.033333335
.065934069
.46
.666666687
.99999994
1
1.02479339
The strange part is the "0" and ".0" entries. I changed the column formatting in order to see more decimal places ...
SQL> col QTY_CHARGED for 9999999.9999999999999999999999999999999999999999999999999999999
SQL> select distinct QTY_CHARGED from eb_inv_item where QTY_CHARGED
< 5;
QTY_CHARGED
----------------------------------------------------------------
.0000000000000000000000000000000000000000000000000000000
.0000000000000000000000000000000000000000000000000000000
.0329670347273350000000000000000000000000000000000000000
.0333300000000000000000000000000000000000000000000000000
.0333333350718021000000000000000000000000000000000000000
.0659340694546700000000000000000000000000000000000000000
.4600000000000000000000000000000000000000000000000000000
.6666666865348820000000000000000000000000000000000000000
.9999999403953550000000000000000000000000000000000000000
1.0000000000000000000000000000000000000000000000000000000
1.0247933864593500000000000000000000000000000000000000000
To me they look like the same value but they are returned as distinctly different values.
Can anyone explain this to me.
I am unsure as to how the data got into this column in the first place.
Thanks
Glen
-- Glen Mitchell NZ Phone: +64 9 3730400 Energy Research Lab URL: http://www.peace.com Peace Software Email: [EMAIL PROTECTED]
