SQLTeam.com | Weblogs | Forums

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

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')
where a.CUSTOMER_SITE_USE_ID = :order.invoice_to_org_id)

Could anybody suggest a solution

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

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