All Forums Analytics
NMadson-3173 44 posts Joined 09/06
20 Jul 2007
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_dtFrom dupcs1.EPT_EXPS_SCNRO s, (Select scnro_ky, Count(acct_ky) As RecNumFrom dupcs1.EPT_EXPS_SCNRO_ACCT_METRICS samGroup By 1) iWhere i.scnro_ky = s.scnro_ky;

Barry-1604 176 posts Joined 07/05
20 Jul 2007

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.

You must sign in to leave a comment.