Reputation: 4435
I am running a simple sum and conversion to currency on a NUMBER field in an Oracle database.
My query is:
select
TO_CHAR(eve.data_entry_date, 'yyyy-mm'), wtc.description as WORK_TYPE,
TO_CHAR(sum(sev.amount),'$999,999.99') AS "Total Invoice Amount"
from
EVENT eve,
SOW_EVENT sev,
WORK_TYPE_CODE wtc,
SOW_WORK_TYPE_XREF swt,
WORK_TYPE_ITEM_CODE wti
where
eve.event_number_id = sev.event_number_id
and sev.WORK_TYPE_CODE = WORK_TYPE_CODE
and sev.event_number_id = swt.event_number_id
group by
TO_CHAR(eve.data_entry_date, 'yyyy-mm'), wtc.description
The query runs successfully, however the amounts showing up in the "Total Invoice Amount" column are returning hashes like:
Year-Month WORK_TYPE Total Invoice AMount
2019-01 Physical Work ############
2019-01 Technical Work ############
I had thought I just needed to resize the column, but that didn't work. When I just run:
sum(sev.amount)
it populates the amounts, just not formatted as currency as the 'amount' column is a number column. Any idea why I am getting the hashes when I format to currency?
Upvotes: 1
Views: 1332
Reputation: 191570
All number format models cause the number to be rounded to the specified number of significant digits. If a value has more significant digits to the left of the decimal place than are specified in the format, then pound signs (#) replace the value. This event typically occurs when you are using
TO_CHAR
with a restrictive number format string, causing a rounding operation.
Your format mask needs enough digit placeholders for the highest value you expect to see. At the moment the values seem to be above a million.
Upvotes: 2