My goof... I mis-typed your FROM clause. I should have written:

   select count(*), cast(pending_date as date) as "Reclaimed" -
   from volumes as pending where status='PENDING' -
   group by pending_date

But not my question becomes, why do you have "from volumes as pending"
instead of just "from volumes"? I would just write:

   select count(*), cast(pending_date as date) as "Reclaimed" -
   from volumes where status='PENDING' group by pending_date

Regards,

Andy

Andy Raibeck
IBM Tivoli Systems
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.




Andrew Raibeck/Tucson/IBM@IBMUS
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
09/24/2001 10:55
Please respond to "ADSM: Dist Stor Manager"


        To:     ADSM-L AT VM.MARIST DOT EDU
        cc:
        Subject:        Re: SQL query - GROUP on derived value?



I think there are a couple of problems that stand out:

1) For the pending_date, you want to say

   as "Reclaimed"

not

   as 'Reclaimed'

(note the double versus single quotes)

2) In the "group by" clause, use the actual column name PENDING_DATE

So....

   select count(*), cast(pending_date as date) as "Reclaimed" -
   from volumes as "Pending" where status='PENDING' -
   group by pending_date

Regards,

Andy

Andy Raibeck
IBM Tivoli Systems
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.




Ted Byrne <tbyrne AT DSSCORP DOT COM>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
09/24/2001 10:00
Please respond to "ADSM: Dist Stor Manager"


        To:     ADSM-L AT VM.MARIST DOT EDU
        cc:
        Subject:        SQL query - GROUP on derived value?



Is it possible to group query output by a value that is *not* directly
present in a column in a table?  What I'm trying to achieve is something
like producing a tally of volumes that are pending, grouped by day that
they went pending.

If I try something like this:
        select count(*),cast(pending_date as date) as 'Reclaimed' -
        from volumes as pending where status='PENDING' -
        group by 'Reclaimed'

TSM does not care for it:
        ANR2906E Unexpected SQL literal token - 'Reclaimed'.

If I change the "group by" to refer to the pending_date column, the query
is processed, but the pending_date is a TIMESTAMP with a time component as
well.  There might be 10 different pending_date values that fall at
various
times during a particular day.

This can certainly be done using perl, but I'd like to stick to "pure" SQL
if I can, to make it available as a script from within TSM.

Any suggestions?

If this is an RTFM item, I'll willingly take my time in the corner...  (A
referral to a SQL reference would be helpful; perhaps I'm just not looking
at the right sections of the references that I've consulted.)

Thanks,

Ted