Format to provide comma separators?

Analytics
Enthusiast

Format to provide comma separators?

Can anyone tell me how to get the FORMAT function to supply comma separators? The following syntax doesn't work.

Select s.scnro_ky
, Substr(scnro_desc,1,20)
, i.RecNum (Format 'ZZZ,ZZZ,ZZ9')
, scnro_bgn_dt
, scnro_end_dt
, model_ky
, unit_cost_set_ky
, most_recent_run_dt_tm
, insrt_dt_tm
, updt_dt_tm
, au_dt
From dupcs1.EPT_EXPS_SCNRO s
,
(
Select scnro_ky
, Count(acct_ky) As RecNum
From dupcs1.EPT_EXPS_SCNRO_ACCT_METRICS sam
Group By 1) i
Where i.scnro_ky = s.scnro_ky;
1 REPLY
Enthusiast

Re: Format to provide comma separators?

If you are using SQL Assistant, the reason that the format was ignored was because ODBC will ignore format statements. It instead will send back each field based on the data type in the resultset.

In order to have it come back in the format that you specified, cast it to character in the SQL:

, CAST(CAST(i.RecNum as Format 'ZZZ,ZZZ,ZZ9') AS CHAR(11))

Hope that helps.