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
|