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:[EMAIL PROTECTED]] > Sent: Thursday, September 19, 2002 5:35 AM > To: [EMAIL PROTECTED] > 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
