Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Format decimals result. Select

2699420Jun 23 2014 — edited Jun 24 2014

Hello all!

I'm new in the forum. I hope you can help me with my problem.

I'm working with Oracle database and I have a select in which I need to return a number with X decimals. Until know I did:

Select cast ((subselect so on) as number(5,2)) field,

      ....

The problem is that if I have '0' I get '0', but I'd need '0.00'. So it only works if the numbers have decimals.

I was looking in the forum and I get other threads where people talk about to_char. It works but the result is not a  decimal type column, but string one.

I thought I could use to_number, so I tried:

Select to_number(To_char((myNumber),'9990.99'),'999999999D00') ...

and another formats but I always get an integer if the number has not decimals.

I'd need results like this:

0     -> 0.00

1.5  -> 1.50

8     -> 8.00

Could someone help me? Is it possible to format a number in this way and return a number (not a string) using Oracle?

Thank you so much.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 22 2014
Added on Jun 23 2014
13 comments
5,192 views