CODEWITHSUNDEEP
sqloracle-databasenumbersscaleprecision
Mathew Linton
Mathew Linton

Reputation: 33

oracle: query to have as output scale and precision for numeric columns - field datatype = number

I have a column with number datatype. In this column I have values with scale and precision but also only scale is available. I would like when i query the data to have in the output scale and precision for all the fields.

create table test ( column number );

insert into test values (123);
insert into test values (0);
insert into test values (15.06);
insert into test values (78.78);
insert into test values (12.3);
commit;

column

   123
     0
 15.06
 78.78
  12.3

when I query the data I would like to have an output as:

column

123.00
  0.00
 15.06
 78.78
 12.30

Can you indicate how should i write the query in order to have the above output? The output should be also numeric. The precision part is not fixed.

Upvotes: 0

Views: 946

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

Convert to the format you want:

select to_char(col, '990.00')
from t;

Here is a db<>fiddle.

You can control the scale by adjusting the pattern. For instance, for four decimal places:

select to_char(col, '990' || rpad('.', 4 + 1, '0'))
from test;

I do not know how to control the result set so the value is number.

Upvotes: 2

Nikhil
Nikhil

Reputation: 3950

this will work:

select to_char("a", '000.00') from Table1;

http://sqlfiddle.com/#!4/16599a/2

Upvotes: 1

Related Questions