Ted,
I've got it at the end but what a nasty solution :-O
select count(*),startdate -
from volumes,client_schedules -
where startdate=date(pending_date) -
and domain_name='SCHED' -
group by startdate
To achieve this I defined dummy domain ('SCHED') without nodes and created
one schedule per day there.
The only way I was able to create dummy schedules was through cron
dsmadmc -id=<user> -pa=<pass> def sch sched `date +%Y-%m-%d`
startdate="'`date +%m/%d/%Y`'"
Right now I cannot do more.
Zlatko Krastev
IT Consultant
Ted Byrne <[EMAIL PROTECTED]> on 24.09.2001 22:00:29
Please respond to "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
cc:
Subject: Re: SQL query - GROUP on derived value?
Andy,
Thanks for the feedback. I may not have been clear in stating my original
question, and now that I've gone back and re-read your ADSM SQL reference,
I think what I'm looking for may not be possible within the syntax of the
SQL Query.
Just to clarify:
>But not my question becomes, why do you have
>"from volumes as pending" instead of just "from volumes"?
This was a typo on my part...
>I would just write:
>select count(*), cast(pending_date as date) as "Reclaimed" -
>from volumes where status='PENDING' group by pending_date
When running this query, the output looks like this:
Unnamed[1] Reclaimed
----------- ----------
1 2001-09-17
(more rows here)
1 2001-09-17
(more rows here)
1 2001-09-24
(more rows here)
2 2001-09-24
(more rows here)
1 2001-09-24
There are multiple results with the same value in "Reclaimed"; the
PENDING_DATE is different because they became pending at different times
during the same calendar day.
What I'm trying to produce would be more like the following:
Unnamed[1] Reclaimed
----------- ----------
7 2001-09-17
10 2001-09-23
11 2001-09-24
The syntax diagram from "Using the ADSM SQL Interface" seems to exclude
anything but a column name that is part of a table from being used for
grouping.
'-GROUP BY--+------------+--.--column_name--'
'-table_name-'
Thanks,
Ted
