Re: SQL query - GROUP on derived value?

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 <tbyrne AT DSSCORP DOT COM> on 24.09.2001 22:00:29
Please respond to "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
To:     ADSM-L AT VM.MARIST DOT EDU
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