SQLTeam.com | Weblogs | Forums

Thousand separator in SQL Query, with TO_CHAR function gives error in sum function

sql2008
#1

I want to show the amount in a thousand separator format. I have done it using TO_CHAR but it is limiting me to SUM

two column values. For example below query works fine without thousand separator but gives error otherwise

"could not be evaluated because of error ORA-01722: invalid number"

=( select
to_char(sum(unapplied_amount * nvl(a.EXCHANGE_RATE,1)),'999,999,999.99') +
to_char(sum(on_Account * nvl(a.EXCHANGE_RATE,1)),'999,999,999.99')
from XX_SF_COLLECTION_V a
where a.CUSTOMER_SITE_USE_ID = :order.invoice_to_org_id)

Could anybody suggest a solution

0 Likes

#2

This is Ms sql forum not Oracle but someone here might be able to answer.
But you can use the sum function on a char/string vaue

0 Likes

#3
Declare @t table (
		UnAppliedAmount numeric(10,2),
	    ExchangeRate numeric(7,4),
		OnAccount numeric(10,2))

insert into @t values
(10000, 1.25, 5000),
(12000, 0.75, 25000),
(6000, null, 6000)

select cast(convert(numeric(10,2), sum(UnAppliedAmount * IsNull(ExchangeRate,1)) + sum(OnAccount * IsNull(ExchangeRate,1))) as varchar(12))
  from @t
0 Likes