Hi Bill,

the device class is needed since the output is parsed by an awk script for
further processing. Still, you pointed me into the right direction since I
remembered we were creating a second device class on the same librarey for
testing the other day. So the select shows scratch tapes for both these
device classes. I removed the devclass and it works perfectly.

Thanks a lot

Paul,

the device class does not come in here, so this select works w/o problems.

Thanks for the fast reply

Lars

On 19.09.2002 19:33:16 "ADSM: Dist Stor Manager" wrote:

> I run something similar every day.
>
> I have 1 devclass per library and the command works fine without
duplicating
> the count of scratch tapes.
>
> Do you have multiple device classes in the same library? My scratch are
not
> assigned to a specific device class they are scratch for the entire
library.
>
> If you don't require the deviceclass in the output you could simplify:
>
> "select library_name, count(*) as volumes from libvolumes
> where upper(status) like 'SCRATCH%' and library_name='$i'
> group by library_name"
>
>
> You could also improve the scratch query by changing
>
>                 upper(status) like 'SCRATCH%'
>
> to
>
>                 status='Scratch'
>
> I hope this helps.
>
> Bill
>
>
> -----Original Message-----
> From: Lars Bebensee [mailto:LBebensee AT HAITEC DOT DE]
> Sent: Thursday, September 19, 2002 5:35 AM
> To: ADSM-L AT VM.MARIST DOT EDU
> Subject: Select for Scratches
>
> Hi folks,
>
> I am not that good with select queries yes, so I will have to ask one of
> the SQL gurus here:
> we wanted to show how many scratch tapes each library has with a query
like
> this:
>
> "select a.library_name,count(*) as volumes, b.devtype from libvolumes as
a,
> devclasses as b where upper(status) like 'SCRATCH%' and a.library_name
='$i'
> and a.library_name=b.library_name group by a.library_name,b.devtype"
>
> $i will be filled with the name of the library before. The funny thing,
it
> shows exactly as twice as many scratches. Is there someone amongst you
who
> can exctract this little monster for me and show me what I am doing
wrong.
>
> Thanks for helping out
>
> Lars