Search code examples
sqloraclesumcurrency-formatting

Formatting Number Field to Currency Using TO_CHAR returning hash characters

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?

Solution

  • From the documentation:

    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.