Categories

# 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! ## By Laurent Schneider

Oracle Certified Master

## 2 replies on “How many decimals do you need?”

Thank You for good example
perhaps

!=

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

😉

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)); DUM --- X ```

!