Formatting Numeric Fields with commas

[msteffens]

Hello,

Does anyone know how to format a numeric field with commas? My sql statement returns the number, but without commas. Here's my statement:

cast(sum(bytes/1024/1024) as decimal(18,0)) as "MB Transferred"

Looks like this: xxxxxx
I want it like this: xxx,xxx

I can't find any documentation on how to do it and I found nothing in the Forums.

Thanks in advance for any information!

Marty

 

[toxy13]

try

cast(sum(bytes/1024/1024) as decimal(18,3)) as "MB Transferred"

or which select statement you mean?

 

[Graeme]

To revisit this topic ...

I have noticed in the bundled reports (e.g. Reports/Operational Reports/Daily Report) that some numbers are formatted with commas as thousands separators, e.g. xxx,xxx,xxx.xx
Is it possible to do this in reports (or report segments) that you add yourself? If so, how?

For example -
I am wanting a section to present node backup summary information ...

select entity AS "Node Name",CAST(sum(bytes/1024/1024) AS decimal(9,2)) AS "MB Transfered",CAST(sum(examined) AS decimal(10,0)) AS "Objects Examined",CAST(sum(affected) AS decimal(10,0)) AS "Objects Backed Up",CAST(sum(failed) AS decimal(10,0)) AS "Objects Failed",SUBSTR (CAST(min (start_time) AS char(29)),1,10) AS "Start Date",SUBSTR (CAST(min (start_time) AS char(29)),12,8 ) AS "Start Time",SUBSTR (CAST(max (end_time) AS char(29)),1,10) AS "End Date",SUBSTR (CAST(max (end_time) AS char(29)),12,8 ) AS "End Time" FROM summary WHERE activity='BACKUP' AND start_time>=current_timestamp - 24 hours AND entity IN (SELECT node_name FROM nodes) GROUP BY entity

... and I would like the things such as

CAST(sum(bytes/1024/1024) AS decimal(9,2)) AS "MB Transfered"

to have commas as thousands separators, how would I do it (if it can be done)?

Thanks for your time.

TTFN.,
Graeme

 

[droach]

I know, old thread, but anyone figured out how to get commas into the output from select statements? The suggestion listed here does not work for me.

 

[RobertL]

I know, old thread, but anyone figured out how to get commas into the output from select statements? The suggestion listed here does not work for me.

I researched this, and there seem to be 2 techniques using SELECT syntax. Neither work for me.

The first is a huge long cumbersome technique using REPLACE, which is ugly and error-prone. The second is using a CONVERT to data type of MONEY, however I keep getting errors with that, so I'm not sure TSM supports the MONEY data type.

It must be possible, because stuff like "Q SESSION" has commas (wish it wasn't in the SESSION_NUMBER, tho).
Everyone talking about SQL/SELECT said the commas should be added in the "presentation" layer, but I'm just a TSM Server admin with a command prompt -- no presentation layer that I know of. So, still a mystery to me.

 

[droach]

Not sure if someone has already figured this out, but here is an example of a way to put commas in your select statement's results:

select char(stgpool_name,22) as stgpool,-
substr(varchar_format(sum(physical_mb)/1024,'999,999,999,999,999,999'),1,24) as Physical_GB ,-
substr(varchar_format(sum(logical_mb)/1024,'999,999,999,999,999,999'),1,24) as Logical_gb,-
substr(varchar_format(sum(num_files),'999,999,999,999,999,999'),1,24) as Objects -
from occupancy group by stgpool_name order by 1​