I'm working on a query which returns numeric values (currency). Some of the values are whole numbers and are being displayed as 3, 5, 2 etc whilst other numbers are coming up like 2.52, 4.50 etc.
How can I force oracle to always show the decimal places?
Thanks
TO_CHAR(pAmount, '9,999,999.99');
http://www.techonthenet.com/oracle/functions/to_char.php
http://www.ss64.com/orasyntax/to_char.html
To enhance the answers already given, you can use:
TO_CHAR(your_value,'fm999.99') to prevent leading spaces
____3.45 becomes 3.45 (_ indicates whitespace)
TO_CHAR(your_value,'fm990.99') to force values less than 1 to show a leading zero
.52 becomes 0.52
TO_CHAR(your_value,'fm990.00') to force 2 decimal places, even if 0
6.3 becomes 6.30
(TO_CHAR(your_value,'fm990.00')||'%') to add a percentage sign
18.6 becomes 18.60%
source: https://community.oracle.com/thread/968373?start=0&tstart=0
The display and formatting of the data should be handled at the presentation layer - not the data one.
Use the facilities provided by your front end to format the values as you see fit.
The to_char fixes the decimal issue but you have to be certain about the length. If it is longer than the format provided, it will show the number as ####. If the number is shorter, then it will leave spaces before the number. e.g
to_char(123.45),'99.00') will show ####
and
to_char(123.45),'999999.00') will show ' 123.45'.
So, if you have to export the results to CSV or Excel, these numbers will be treated as string.
So, I have not found any solution to it.
In SQL*Plus you can use the COLUMN directive to specify formatting on a per-column basis, separate from the query itself. That way you keep your query "clean" for possible other uses and still get your formatting. (In SQL*Plus at least...)
e.g
COLUMN SAL FORMAT 99,990.99
Google for "SQL*Plus User's Guide and Reference" and you should get links to the Oracle location for your Oracle version. 10.1 is here if that'll do. They'll probably all be about the same, mind you: I don't think SQL*Plus has changed much since I learned it in 1988 on Oracle 5.1.17...
