Would this SQL give you what you want?

select library_name, volume_name, status, last_use from libvolumes where
status='Private' and volume_name not in (select volume_name from volumes)

That is, any libvolumes in Private status, that are not in the volume table.

Paul.

From: Pieter Groen <pieter.groen AT UCI.KUN DOT NL>
Reply-To: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Re: Oggetto: Re: Select ??????????
Date: Wed, 27 Sep 2000 12:11:54 +0200

You are right. The volumes with nothing in last_use column don't show up.
In my opinion this is A BUG!
IBM or Tivoli-people please respond?

Greetings,
Pieter

Alessio Prodon schreef:
>
> Thank you for reply but with this command I show it :
>
> ANR2034E SELECT: No match found using this criteria.
> ANS8001I Return code 11.
>
> I don't know what appening !!!
>
> Alessio Prodon
>
> Pieter Groen <pieter.groen AT UCI.KUN DOT NL> il 27/09/2000 10.48.32
>
> Per favore, rispondere a "ADSM: Dist Stor Manager"
<ADSM-L AT VM.MARIST DOT EDU>
>
> Per:  ADSM-L AT VM.MARIST DOT EDU
> Cc:    (ccr: Alessio Prodon/Centro di Gestione/G-Net/IT)
> Oggetto:  Re: Select ??????????
>
> You should not compare upper(something) with 'Data', because 'ata' is
lower
> case!
>
> This works:
>
>  select * from libvolumes where last_use != 'Data'
>
>   LIBRARY_NAME: 3575LIB
>    VOLUME_NAME: A00215
>         STATUS: Private
>       LAST_USE: DbBackup
>   HOME_ELEMENT: 273
> CLEANINGS_LEFT: 0
>
> Greetings,
> Pieter
>
> Alessio Prodon schreef:
> >
> > If I entry " q libv "  I have :
> >
> > adsm> q libv
> >
> > Library Name     Volume Name     Status                Last Use
> > ------------     -----------     -----------------     ---------
> > LIB7332          DS0001          Scratch
> > LIB7332          DS0003          Private               Data
> > LIB7332          DS0004          Scratch
> > LIB7332          DS0005          Private
> >
> > With select I must show  only " ds0005 "  (because it's in private
state and
> > last_use isn't = data), but if I try with this select :
> >
> > select library_name, volume_name, status,last_use from libvolumes
where
> > upper(status) = 'PRIVATE' and upper(last_use) != 'Data'  ,  I show
> >
> > LIBRARY_NAME           VOLUME_NAME            STATUS
LAST_USE
> > ------------------     ------------------     ------------------
> ----------
> > LIB7332                DS0003                 Private
Data
> >
> > It isn't correctly !!!!
> >
> > Any suggestion ???
> >
> > Alessio Prodon
> >
> > I try with this command :
> >
> > ---------------------- Inoltrato da Alessio Prodon/Centro di
Gestione/G-Net/IT
> > il 27/09/2000 09.59 ---------------------------
> >
> > Horst Scherzer <Horst.Scherzer AT UNIVIE.AC DOT AT> il 27/09/2000 09.47.53
> >
> > Per favore, rispondere a "ADSM: Dist Stor Manager"
<ADSM-L AT VM.MARIST DOT EDU>
> >
> > Per:  ADSM-L AT VM.MARIST DOT EDU
> > Cc:    (ccr: Alessio Prodon/Centro di Gestione/G-Net/IT)
> > Oggetto:  Re: Select ??????????
> >
> > Alessio Prodon wrote:
> > >
> > > Hello !!!
> > >
> > > I'm running ADSM 3.1.2.55 on AIX 4.3.3. I would like a tape's list
in
> private
> > > status, not associate to the stgpool.
> > >
> > > I see you example :
> > >
> > > adsm> q libv
> > >
> > > Library Name     Volume Name     Status                Last Use
> > > ------------     -----------     -----------------     ---------
> > > LIB7332          DS0001          Private               Data
> > > LIB7332          DS0003          Private               Data
> > > LIB7332          DS0004          Scratch
> > > LIB7332          DS0005          Private
> > >
> > > I try with select statement :
> > >
> > > select * from libvolumes where status='Private' and last_use <>
'Data'
> > >
> > > This command doesn't  work !!!
> > >
> > > Please, let me know how can I solve this problem.
> > >
> > > Thank you in advance.
> > >
> > > Alessio Prodon
> >
> > ANS5100I Session established with server BACKUP: AIX-RS/6000
> > ANS5101I Server command: 'select library_name, volume_name, status,
> > last_use from libvolumes where upper(status) = 'PRIVATE' and
> > upper(last_use) != 'DATA''
> >
> > LIBRARY_NAME           VOLUME_NAME            STATUS         LAST_USE
> > ------------------     ------------------     ----------
----------
> > IBM3494                A00557                 Private        Export
> > IBM3494                A00598                 Private        Export
> > IBM3494                B00100                 Private        DbBackup
> > IBM3494                B00101                 Private        DbBackup
> > IBM3494                B00102                 Private        DbBackup
> > IBM3494                B00103                 Private        DbBackup
> > IBM3494                B00104                 Private        DbBackup
> > IBM3494                B00105                 Private        DbBackup
> > IBM3494                B00106                 Private        DbBackup
> >
> > ANS5103I Highest return code was 0.
> >
> > Hth,
> > --
> >
> >
------------------------------------------------------------------------
> > Horst SCHERZER                       mailto:
Horst.Scherzer AT univie.ac DOT at
> > Zentraler Informatikdienst Uni-Wien           Fax:   (+43 1) 4277
x9140
> > Universitaetsstr.7                          Phone:   (+43 1) 4277
x14053
> > A-1010 Vienna, Austria              URL:
http://mailbox.univie.ac.at/~sc
> >
------------------------------------------------------------------------

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at
http://profiles.msn.com.