Re: SQL Help

This is possible, ish..

I posted a similar question to the list a while ago, and the only select
query I ended up with was this set of multiple queries, one for each
interval of a day going back 5 days;


select stgpool_name,devclass_name,'<=1 day',count(*) as Total from
volumes where status='PENDING' and
cast((current_timestamp-PENDING_DATE)days as decimal)<=1 group by
stgpool_name,devclass_name

select stgpool_name,devclass_name,'>1 <=2 days',count(*) as Total from
volumes where status='PENDING' and
cast((current_timestamp-PENDING_DATE)days as decimal)>1 and
cast((current_timestamp-PENDING_DATE)days as decimal)<=2 group by
stgpool_name,devclass_name

select stgpool_name,devclass_name,'>2 <=3 days',count(*) as Total from
volumes where status='PENDING' and
cast((current_timestamp-PENDING_DATE)days as decimal)>2 and
cast((current_timestamp-PENDING_DATE)days as decimal)<=3 group by
stgpool_name,devclass_name

select stgpool_name,devclass_name,'>3 <=4 days',count(*) as Total from
volumes where status='PENDING' and
cast((current_timestamp-PENDING_DATE)days as decimal)>3 and
cast((current_timestamp-PENDING_DATE)days as decimal)<=4 group by
stgpool_name,devclass_name 

select stgpool_name,devclass_name,'>4 <=5 days',count(*) as Total from
volumes where status='PENDING' and
cast((current_timestamp-PENDING_DATE)days as decimal)>4 and
cast((current_timestamp-PENDING_DATE)days as decimal)<=5 group by
stgpool_name,devclass_name


Matt.

_-'-_
  -|-

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Prather, Wanda
Sent: Wednesday, December 01, 2004 5:40 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: SQL Help

Andy, 

I don't think this is possible.

DISTINCT doesn't help
TSM always responds "unknown SQL column" when a results  column is
included in the group by
It won't take a function in the GROUP BY
And It won't let me do a SELECT inside the FROM

 

-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
Andrew Raibeck
Sent: Wednesday, December 01, 2004 10:37 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: SQL Help


Try adding the DISTINCT keyword between SELECT and DATE(PENDING_DATE),
i.e.

   select distinct date(pending_date) ...

You'll want to verify that the results are correct by matching up the
counts against the results of the select without DISTINCT. For example,
using the output you show below, I'd expect to see a single line showing
a
count of 5 for 11/27/2004.

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.ibm DOT com

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.

"ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU> wrote on 12/01/2004
08:18:16:

> That does not give me the desired results:
>
> tsm: ULTSM> select date(pending_date) as "Date",count(*) from volumes
> where -
> cont>  status='PENDING' group by pending_date
>
>       Date      Unnamed[2]
> ----------     -----------
> 2004-11-27               1
> 2004-11-27               1
> 2004-11-27               1
> 2004-11-27               1
> 2004-11-27               1
>
> group by pending_date groups by the time stamp not the date stamp.  I
> want a count of pending tapes by date not by second.
>
> >>> gretchen AT PRINCETON DOT EDU 12/1/2004 10:02:40 AM >>>
> The group by statement is incorrect, use the following:
>
> group by pending_date
>
> the conversion to date format is already taken care of
> in the first part of the select statement.
>
> David E Ehresman wrote:
>
> > tsm: ULTSM> select date(pending_date) as "Date",count(*) from
> volumes
> > where -
> > cont> status='PENDING' group by "Date"
> > ANR2940E The reference 'Date' is an unknown SQL column name.
> >
> >                                                                   |
> >
> > .........................................................V.....
> >          e",count(*) from volumes where status='PENDING' group by
> > "Date"
> >
> > ANS8001I Return code 3.
> >
> >
> >
> >>>>mark.stapleton AT BERBEE DOT COM 11/30/2004 9:09:03 AM >>>
> >
> > From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On
> > Behalf Of David E Ehresman
> >
> >>I'm trying to get a count of tapes pending by date.  I'm using:
> >>       select date(pending_date),count(*) from volumes where
> >>status='PENDING' -
> >>  group by date(pending_date)
> >>but I get the error message:
> >>  ANR2904E Unexpected SQL key word token - 'DATE'.
> >>pointing to the date in the group by clause.
> >>
> >>Anyone know how to get a pending count by date?
> >
> >
> > Try
> >
> >   select date(pending_date) as "Date",count(*) from volumes where
> > status='PENDING' group by "Date"
> >
> > --
> > Mark Stapleton (stapleton AT berbee DOT com)
> > Berbee Information Networks
> > Office 262.521.5627


___________________________ Disclaimer Notice __________________________
This message and any attachments are confidential and should only be read by 
those to whom they are addressed. If you are not the intended recipient, please 
contact us, delete the message from your computer and destroy any copies. Any 
distribution or copying without our prior permission is prohibited.

Internet communications are not always secure and therefore Powergen Retail 
Limited does not accept legal responsibility for this message. The recipient is 
responsible for verifying its authenticity before acting on the contents. Any 
views or opinions presented are solely those of the author and do not 
necessarily represent those of Powergen Retail Limited. 

Registered addresses:

Powergen Retail Limited, Westwood Way, Westwood Business Park, Coventry, CV4 
8LG.
Registered in England and Wales No: 3407430

Telephone +44 (0) 2476 42 4000
Fax +44 (0) 2476 42 5432