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: [EMAIL PROTECTED]
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 <[EMAIL PROTECTED]>
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
09/24/2001 10:00
Please respond to "ADSM: Dist Stor Manager"
To: [EMAIL PROTECTED]
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
