I tried the same thing on an Oracle database:

select count(*) from sometable
group by hour(sometimestampfield)

I get an 'invalid column name' error.

Functions that operate on fields that have
a type other than 'timestamp' work as
one would expect:

select count(*) from sometable
group by lower(somecharfield)

I don't understand this either but it seems
to be a general SQL restriction and not a
problem with the TSM SQL interface.

Alexander

Steve Harris wrote:

>
> Hi All,
>
> I just realized I can use the summary table to get tape mount stats.  However 
> I can't get my sql to work
> I'm trying
>         select hour(end_time) as "Hour", count(*)
>         from summary
>         where activity= 'TAPE MOUNT'
>                 and date(end_time) = current date - 1 day
>         group by hour(end_time)
>
> and TSM is complaining about the group by clause.
>
> ANR2904E Unexpected SQL key word token - 'HOUR'.
>
>                                                           |
>          .................................................V.............
>          nd date(end_time) =current date - 1 day group by hour(end_time)
>
> I've also tried 'group by 1' and 'group by "Hour"' but that doesn't work 
> either.
>
> This is fairly standard SQL, anyone know what I'm doing wrong?
>
>
> Steve Harris
> AIX and TSM Administrator
> Queensland Health, Brisbane Australia
>
>
>
>
>
> **********************************************************************
> This e-mail, including any attachments sent with it, is confidential
> and for the sole use of the intended recipient(s). This confidentiality
> is not waived or lost if you receive it and you are not the intended
> recipient(s), or if it is transmitted/ received in error.
>
> Any unauthorised use, alteration, disclosure, distribution or review
> of this e-mail is prohibited.  It may be subject to a statutory duty of
> confidentiality if it relates to health service matters.
>
> If you are not the intended recipient(s), or if you have received this
> e-mail in error, you are asked to immediately notify the sender by
> telephone or by return e-mail.  You should also delete this e-mail
> message and destroy any hard copies produced.
> **********************************************************************

--
-----------------------------------------------
-----------------------------------------------
Alexander Verkooijen        (alexander AT sara DOT nl)
Alexander Verkooijen        (alexander AT sara DOT nl)
Senior Systems Programmer
SARA High Performance Computing