Storing trailing zeros in mantissa

From: Norman Dunbar <Norman.Dunbar_at_lfs.co.uk>
Date: Fri, 1 Aug 2003 15:56:19 +0100
Message-ID: <E2F6A70FE45242488C865C3BC1245DA703FB1F21@lnewton.leeds.lfs.co.uk>

Jeff,

how Oracle stores numbers is it's own business, how your app retrieves them is what causes your 'problem' :

CREATE TABLE TEST (a NUMBER(18,2))
Table created

INSERT INTO TEST VALUES (1)
1 row inserted

INSERT INTO TEST VALUES (2.1)
1 row inserted

INSERT INTO TEST VALUES (2.20)
1 row inserted

INSERT INTO TEST VALUES (3.33)
1 row inserted

INSERT INTO TEST VALUES (4)
1 row inserted

INSERT INTO TEST VALUES (5.25)
1 row inserted

INSERT INTO TEST VALUES (6.7)
1 row inserted

INSERT INTO TEST VALUES (1234567890.10)
1 row inserted

COMMIT
Commit complete

SQL> SELECT a AS the_number FROM TEST;

THE_NUMBER

1234567890

8 rows selected.

SQL> SELECT TO_CHAR(a, '9999999999999999.99') AS the_number_formatted

FROM TEST; THE_NUMBER_FORMATTED

                1.00
                2.10
                2.20
                3.33
                4.00
                5.25
                6.70
       1234567890.10

8 rows selected.

SQL> SELECT TO_CHAR(a, '0000000000000000.99') AS the_number_formatted

FROM TEST; THE_NUMBER_FORMATTED

 0000000000000001.00
 0000000000000002.10
 0000000000000002.20
 0000000000000003.33
 0000000000000004.00
 0000000000000005.25
 0000000000000006.70
 0000001234567890.10

8 rows selected.

So Oracle *is* storing the number correctly but your command to select it from the database is incorrect. The problem being, when the select is done, you asked for a number but the display is unable to show you that number 'as it is' so there is a conversion to a charcter string performed and as no conversion mask has been defined, you get no leading zeros and no trailing zeros.

Change your application's SQL to convert the numbers implicitly to use a format mask in to_char() - if you can that is !

On the other hand, what sort of scientific application cant tell that 10.2 is not equal to 10.20 ?
It must be comparing numbers as strings rather than as numbers.

HTH Cheers,
Norm.

PS. This behaviour is standard in most DBs that I've used over the years, and flat files too - remember COBOL ? :o)

Norman Dunbar
Database/Unix administrator
Lynx Financial Systems Ltd.
mailto:Norman.Dunbar_at_LFS.co.uk

Tel: 0113 289 6265
Fax: 0113 289 3146
URL: http://www.Lynx-FS.com


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

-----Original Message-----
From: zigjst_at_hotmail.com (Jeff Turner) [mailto:zigjst_at_hotmail.com] Posted At: Friday, August 01, 2003 3:04 PM Posted To: server
Conversation: Storing trailing zeros in mantissa Subject: Storing trailing zeros in mantissa

I have a column defined as NUMBER(18,2) and want to store a value of, say, "10.20" in this column. Oracle returns the value to me as "10.2" instead of "10.20", but in my application (scientific in nature) there is a difference in "10.2" and "10.20".

After browsing the ng's, it seems this is normal behavior in Oracle... Is this true? Does anyone know any magical "switches" I can flip to tell Oracle to store trailing zeros?

Thanks,
Jeff Received on Fri Aug 01 2003 - 09:56:19 CDT