Oracle Reserved Decimal digits

method One: Use the TO_CHAR FM format
To_char (Round (data.amount,2), ' FM9999999999999999.00 ') as Amount
The disadvantage is that if the value is 0, it will be displayed as. 00 instead of 0.00.
Another note is that the number of decimal points to the left of the 9 is enough, otherwise the number of the query will be displayed as n symbol "#".
The solution is as follows:
Select Decode (salary,0, ' 0.00 ', (To_char (Round (salary,2), ' fm99999999999999.00 '))) from Can_do;

method Two: Use case and then else end to handle various cases of judgment
Case
When InStr (To_char (Data.amount), '. ') < 1 Then
Data.amount | | '. 00 '
When InStr (To_char (Data.amount), '. ') + 1 = length (Data.amount) Then
Data.amount | | ' 0 '
Else
To_char (Round (Data.amount, 2))
End as Amount_format

method Three: You can use Oracle's own parameter settings
Column Amount Format l9999999999.99
The disadvantage of this method is that the number of 9 in the left of the decimal point in format is known, otherwise the number displayed as ####### #的情况.
Another problem is that when you use column, you need to be aware that the setting takes effect at the session or system level.
Perhaps the value column of a table does not always require all places to be displayed, it is the two digits after the decimal point format, at this time can only use session level, but there is a database connection session time-out problem, if not used to the system level, it is not recommended to use this method.

method Four: How to use To_char+trim
Select Trim (to_char (1234, ' 99999999999999.99 ')) from dual;
or
Select LTrim (Trim (To_char (1234.525, ' 00000000000000.00 ')), ' 0 ') from dual;
The 14 9 or 14 0 formats are used here, and it is recommended to use a 14 9 approach for convenience. The disadvantages of method four are:
If the value is 0, then the conversion is. 00 instead of 0.00, the remedy is, decode.
Another note is that the number of decimal points to the left of the 9 or 0 is enough, the numbers responsible for the query will be displayed as n symbols "#".
As follows:
Select Decode (salary,0, ' 0.00 ', Trim (to_char (Salary, ' 99999999999999.99 '))) from Can_do;
or
Select Decode (salary,0, ' 0.00 ', LTrim (Trim (to_char (Salary, ' 00000000000000.00 ')), ' 0 ')) from Can_do;
Conclusion: It is recommended to use method four in the way of Trim+to_char or method one after the remedy of the way, and preferably using the decimal point to the left N 9 way, do not use 0 of the way, otherwise, it is more than one step trim processing.
That is: Select Decode (salary,0, ' 0.00 ', Trim (to_char (Salary, ' 99999999999999.99 '))) from Can_do;
or
Select Decode (salary,0, ' 0.00 ', (To_char (Round (salary,2), ' fm99999999999999.00 '))) from Can_do;

Oracle Reserved Decimal digits