I just tried this, with some modifications (added the DISTINCT keyword
and the FILESPACE_NAME column):

select distinct b.filespace_name, b.hl_name, b.ll_name from backups b
   where b.node_name='MYNODE' and b.state=inactive_version and
   (b.filespace_name || b.hl_name || b.ll_name) not in
   (select a.filespace_name || a.hl_name || a.ll_name from backups a
   where a.node_name='MYNODE' and a.state=active_version)

It worked for me. But I used a very small test case, so maybe my test
was not entirely comprehensive. This was run on a 5.1.0.0 TSM server.

Regards,

Andy

Andy Raibeck
IBM Software Group
Tivoli Storage Manager Client Development
Internal Notes e-mail: Andrew Raibeck/Tucson/IBM@IBMUS
Internet e-mail: storman AT us.eyebm DOT com (change eye to i to reply)

The only dumb question is the one that goes unasked.
The command line is your friend.
"Good enough" is the enemy of excellence.




"Seay, Paul" <seay_pd AT NAPTHEON DOT COM>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
09/20/2002 14:19
Please respond to "ADSM: Dist Stor Manager"


        To:     ADSM-L AT VM.MARIST DOT EDU
        cc:
        Subject:        Re: SQL - punting for help



It is probably not going to happen with the current database performance,
but this would normally accomplish the task:

select b.hl_name, b.ll_name from backups b where b.node_name='your node'
and
b.state=inactive_version and (b.hl_name || b.ll_name) not in (select
a.hl_name || a.ll_name from backups a where a.node_name='your node' and
a.state=active_version)

I tried it on a single node and it did not find what I was looking for.  I
am going to turn in a problem record on it because it should work.

Paul D. Seay, Jr.
Technical Specialist
Naptheon Inc.
757-688-8180


-----Original Message-----
From: Williams, Tim P {PBSG} [mailto:Tim.Williams AT PBSG DOT COM]
Sent: Friday, September 20, 2002 1:59 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: SQL - punting for help


Ok. This one is teasing me...puzzeling...
trying to get, from the backups table files that ONLY are inactive (if the
file has an active copy, I don't want to have this in the output). have
tried...myself... anyone? have tried state<>state, but this doesn't
work....
? Thanks tim