Shawn,

I think this is where you run into the limitations of the TSM SQL
implementation. I can't think of a way of achieving what you want.
Maybe Andy knows?


On 21 okt 2009, at 23:20, Shawn Drew wrote:

I'm having some trouble with the group by clause.  It seems I can't
group
data that has been "cast"ed

First start with:
select cast((bytes/1024/1024/1024) as decimal(8,2)) AS "GB", cast
(end_time
as date) as "Date" from summary where activity='STGPOOL BACKUP'

                         1794.26     2009-10-19
                         1026.27     2009-10-19
                         1371.60     2009-10-20
                         1524.60     2009-10-20
                         1053.99     2009-10-21
                         1755.08     2009-10-21

As expected, it shows one line per process.  I would like to group
these
by the date so we have a total amount of Gigabytes per day.
But when I add the "sum" and "group by" clauses, it doesn't change the
output:

select sum(CAST((bytes/1024/1024/1024) as decimal(8,2))) AS "GB",
cast(end_time as date) as "Date" from summary where activity='STGPOOL
BACKUP' group by end_time

                         1794.26     2009-10-19
                         1026.27     2009-10-19
                         1371.60     2009-10-20
                         1524.60     2009-10-20
                         1053.99     2009-10-21
                         1755.08     2009-10-21


Also, it doesn't let me group by "Date".  It needs to be grouped by
the
"end_time"
Any ideas to produce what I'm looking for?


TSM 5.5.3 on AIX 6.1

--
Met vriendelijke groeten/Kind regards,

Remco Post
r.post AT plcs DOT nl