How to store both integers and decimals in a column oracle

user_4502950 0
Viewed 82

I am trying to create a column (amount) that has both integers and decimals. But the issue is the column always stores the values as integers.

CREATE TABLE payments(payment_id , Status , payment_date , account_id , currency, amount) AS
SELECT '72b30af0-323e-4931-8fcc-2c08ad8d0b19', 'completed', TO_DATE ('01/10/2017','DD/MM/YYYY'), 2291969088, 'GBP', CAST(10.00 AS NUMBER(20,18)) FROM DUAL UNION ALL
SELECT '1b5341c0-508c-450f-a139-bc898f112fed', 'completed', TO_DATE ('12/11/2014','DD/MM/YYYY'), 7851880663, 'USD', CAST(20 AS NUMBER) FROM DUAL UNION ALL
SELECT '4c8eb68d-3085-4500-b735-371bb7e1ac97','submitted', TO_DATE ('03/07/2016','DD/MM/YYYY'), 5326844767, 'USD', CAST(3.000 AS NUMBER) FROM DUAL UNION ALL
SELECT '33825ef2-d0da-4062-a435-b74a25a850ed','failed', TO_DATE ('19/01/2017','DD/MM/YYYY'), 3668657617, 'EUR', CAST(40 AS NUMBER) FROM DUAL UNION ALL
SELECT '45c10898-d668-4ad1-9730-415254f6e085', 'completed', TO_DATE ('10/05/2017','DD/MM/YYYY'), 9040142052, 'GBP', CAST(30.000 AS NUMBER) FROM DUAL

I have tried using Number alone and Number with precision and scale but the result is the same even with the Decimal data type. Not sure what am I doing wrong here?

oracle sql
2 Answers

The table will store the values in a correct way. Your select is the problem I believe.

select payment_id
       , Status 
       , payment_date 
       , account_id 
       , currency
       , to_char(amount, '99.999')
from payments;

I have used to_char() functionto format your column when selecting. Please correct me if I am wrong. DEMO

user_65650380

Just to expand on answer by @VBoka, providing more thorough example:

the root of your question is basic math. Trailing zeros to the right of the decimal are insignificant. 10 = 10.0 = 10.000000. You are confusing how the value is stored vs how it is displayed by default.

SQL> -- -------- create the tables
SQL> create table my_demo (my_id integer,
  2                        my_value number(5,2));

Table created.

SQL> -- -------- load tables
SQL> insert into my_demo values (1,10);

1 row created.

SQL> insert into my_demo values (2,10.0);

1 row created.

SQL> insert into my_demo values (3,010.00);

1 row created.

SQL> -- -------- Do the query
SQL> select my_id,
  2         my_value,
  3         to_char(my_value,'999.999') formatted_value
  4  from my_demo
  5  order by my_id
  6  ;

     MY_ID   MY_VALUE FORMATTE
---------- ---------- --------
         1         10   10.000
         2         10   10.000
         3         10   10.000

3 rows selected.

And why are you using "CAST(10.00 AS NUMBER"? The 10.00 (without quotes) is already a number. If you were to enclose it in single quotes ("CAST('10.00' AS NUMBER"), then it would be a string that needs to be treated with either CAST or TO_NUMBER.

== new demo for getting just integer values

SQL> -- -------- load tables
SQL> insert into my_demo values (1,10);

1 row created.

SQL> insert into my_demo values (2,10.0);

1 row created.

SQL> insert into my_demo values (3,010.10);

1 row created.

SQL> -- -------- Do the query
SQL> select my_id,
  2         my_value,
  3         to_char(my_value,'999.999') formatted_value
  4  from my_demo
  5  order by my_id
  6  ;

     MY_ID   MY_VALUE FORMATTE
---------- ---------- --------
         1         10   10.000
         2         10   10.000
         3       10.1   10.100

3 rows selected.

SQL> --
SQL> select my_id,
  2         my_value,
  3         to_char(my_value,'999.999') formatted_value
  4  from my_demo
  5  where my_value = trunc(my_value)
  6  order by my_id
  7  ;

     MY_ID   MY_VALUE FORMATTE
---------- ---------- --------
         1         10   10.000
         2         10   10.000

2 rows selected.
user_57325370
Related