CODEWITHSUNDEEP
sqloracle-databasesumcurrency-formatting
Mike
Mike

Reputation: 4435

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?

Upvotes: 1

Views: 1332

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

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.

Upvotes: 2

Related Questions