How many decimals do you need?

Do you user NUMBER or NUMBER(p,s) for your datatypes?

I posted last year about 1!=1

Today I realized this could be solved with the scale :)

SQL> drop table lsc_t
Table dropped.
SQL> create table lsc_t(x number, y number(*,6))
Table created.
SQL> insert into lsc_t values (1/3*3,1/3*3)
1 row created.
SQL> commit
Commit complete.
SQL> select * from lsc_t where x=1
no rows selected.
SQL> select * from lsc_t where y=1

         X          Y
---------- ----------
1.00000000          1

So if you have amounts in US$ or GB£ or CHF, do not use NUMBER, use NUMBER(*,6) or NUMBER(*,2) or whatever is relevant to your business!

2 thoughts on “How many decimals do you need?”

  1. Thank You for good example


    SQL> select * from lsc_t where x=1/3*3;

    X Y
    ———- ———-
    1 1

    SQL> select * from lsc_t where y=1/3*3;

    no rows selected


  2. Hallo Laurent,

    thank you for this useful posting

    SQL> select * from dual where 1/3*3=1;
    no rows selected
    SQL> select * from dual where cast(1/3*3 as number(*,6))=cast(1 as number(*,6));


Leave a Reply

Your email address will not be published.


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>