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