ADSM-L

SQL query - GROUP on derived value?

2001-09-24 13:31:27
Subject: SQL query - GROUP on derived value?
From: Ted Byrne <tbyrne AT DSSCORP DOT COM>
Date: Mon, 24 Sep 2001 13:00:52 -0400
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
<Prev in Thread] Current Thread [Next in Thread>