You may need to design a report with financial information, where the dollar value is in a text field without decimal points.
So how is this possible and not a number field?? Well, in the original business process, legacy systems, the value had always been a real number, then last year, after 20 years, the business began to use cents in payment as well. There is no decision to change the column type, to the change was required in the reports, which contained that column.
The situation is that some number had decimals and others didn't...
In the report the data looked like this:
TOTAL_AMOUNT
-------------------------
33
24.95
12
56.90
I then used NUMERIC_FORMATTING to present the column data in a currency format:
select TO_CHAR(TOTAL_AMOUNT, '9999D99') from TABLE_NAME;
The end result is:
TOTAL_AMOUNT
-------------------------
33.00
24.95
12.00
56.90
When the value is null, a null will be returned.
You could also use:
select TO_CHAR(TOTAL_AMOUNT, '9999.99') from TABLE_NAME;
