That's odd. I put in the same command on my 5.5.3 server:
tsm: TSM21>select sum(CAST((bytes/1024/1024/1024) as decimal(8,2))) AS "
GB", cast(end_time as date) as "Date" from summary where activity='BACKUP'
group by cast(end_time as date)
ANR2904E Unexpected SQL key word token - 'CAST'.
|
.........................................V.....................
summary where activity='BACKUP' group by cast(end_time as date)
ANS8001I Return code 3.
Regards,
Shawn
________________________________________________
Shawn Drew
Internet
cm AT CMOLDRUP DOT DK
Sent by: ADSM-L AT VM.MARIST DOT EDU
10/22/2009 02:48 PM
Please respond to
ADSM-L AT VM.MARIST DOT EDU
To
ADSM-L
cc
Subject
[ADSM-L] SV: [ADSM-L] SV: [ADSM-L] "group by" assistance.
Hi Shawn
I am on 6.1 client/server. However it also worked with version 5's.
Check the 2 queries below...
Regards
Carsten Moldrup
tsm: TEST1_SERVER1>select sum(CAST((bytes/1024/1024/1024) as
decimal(8,2)))
AS "
GB", cast(end_time as date) as "Date" from summary where activity='BACKUP'
grou
p by end_time
GB Date
---------------------------------- -----------
9.00 2009-09-22
0.00 2009-09-22
2.00 2009-09-22
0.00 2009-09-23
2.00 2009-09-23
0.00 2009-09-23
2.00 2009-09-24
2.00 2009-09-25
12.00 2009-09-26
1.00 2009-09-26
0.00 2009-09-26
2.00 2009-09-27
2.00 2009-09-27
0.00 2009-09-28
0.00 2009-09-28
0.00 2009-09-28
2.00 2009-09-29
2.00 2009-09-30
7.00 2009-10-01
2.00 2009-10-01
0.00 2009-10-02
2.00 2009-10-02
0.00 2009-10-02
2.00 2009-10-03
2.00 2009-10-04
2.00 2009-10-05
2.00 2009-10-06
2.00 2009-10-07
0.00 2009-10-08
7.00 2009-10-09
0.00 2009-10-09
2.00 2009-10-09
0.00 2009-10-11
2.00 2009-10-11
2.00 2009-10-12
0.00 2009-10-13
8.00 2009-10-14
2.00 2009-10-14
7.00 2009-10-14
1.00 2009-10-15
2.00 2009-10-16
7.00 2009-10-16
2.00 2009-10-17
1.00 2009-10-18
2.00 2009-10-18
1.00 2009-10-18
2.00 2009-10-19
1.00 2009-10-20
2.00 2009-10-21
1.00 2009-10-21
1.00 2009-10-22
2.00 2009-10-22
tsm: TEST1_SERVER1>
tsm: TEST1_SERVER1>select sum(CAST((bytes/1024/1024/1024) as
decimal(8,2)))
AS "
GB", cast(end_time as date) as "Date" from summary where activity='BACKUP'
group
by cast(end_time as date)
GB Date
---------------------------------- -----------
11.00 2009-09-22
2.00 2009-09-23
2.00 2009-09-24
2.00 2009-09-25
13.00 2009-09-26
4.00 2009-09-27
0.00 2009-09-28
2.00 2009-09-29
2.00 2009-09-30
9.00 2009-10-01
2.00 2009-10-02
2.00 2009-10-03
2.00 2009-10-04
2.00 2009-10-05
2.00 2009-10-06
2.00 2009-10-07
0.00 2009-10-08
9.00 2009-10-09
2.00 2009-10-11
2.00 2009-10-12
0.00 2009-10-13
17.00 2009-10-14
1.00 2009-10-15
9.00 2009-10-16
2.00 2009-10-17
4.00 2009-10-18
2.00 2009-10-19
1.00 2009-10-20
3.00 2009-10-21
3.00 2009-10-22
tsm: TEST1_SERVER1>
-----Oprindelig meddelelse-----
Fra: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] På vegne af
Shawn
Drew
Sendt: 22. oktober 2009 16:40
Til: ADSM-L AT VM.MARIST DOT EDU
Emne: Re: [ADSM-L] SV: [ADSM-L] "group by" assistance.
That didn't work for me. What version of TSM do you have?
Regards,
Shawn
________________________________________________
Shawn Drew
Internet
cm AT CMOLDRUP DOT DK
Sent by: ADSM-L AT VM.MARIST DOT EDU
10/22/2009 03:01 AM
Please respond to
ADSM-L AT VM.MARIST DOT EDU
To
ADSM-L
cc
Subject
[ADSM-L] SV: [ADSM-L] "group by" assistance.
Hi Shawn
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 cast(end_time as date)
..would work
Regards
Carsten Moldrup
-----Oprindelig meddelelse-----
Fra: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] På vegne af
Shawn
Drew
Sendt: 21. oktober 2009 23:20
Til: ADSM-L AT VM.MARIST DOT EDU
Emne: [ADSM-L] "group by" assistance.
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
Regards,
Shawn
________________________________________________
Shawn Drew
Data Protection Engineer
Core IT Production
BNP Paribas RCC, Inc.
Office: 201.850.6998
Mobile: 917.774.8141
This message and any attachments (the "message") is intended solely for
the addressees and is confidential. If you receive this message in error,
please delete it and immediately notify the sender. Any use not in accord
with its purpose, any dissemination or disclosure, either whole or
partial,
is prohibited except formal approval. The internet can not guarantee the
integrity of this message. BNP PARIBAS (and its subsidiaries) shall (will)
not therefore be liable for the message if modified. Please note that
certain
functions and services for BNP Paribas may be performed by BNP Paribas
RCC,
Inc.
|
