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 <[EMAIL PROTECTED]>
>Reply-To: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
>To: [EMAIL PROTECTED]
>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 <[EMAIL PROTECTED]> il 27/09/2000 10.48.32
> >
> > Per favore, rispondere a "ADSM: Dist Stor Manager"
><[EMAIL PROTECTED]>
> >
> > Per: [EMAIL PROTECTED]
> > 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 <[EMAIL PROTECTED]> il 27/09/2000 09.47.53
> > >
> > > Per favore, rispondere a "ADSM: Dist Stor Manager"
><[EMAIL PROTECTED]>
> > >
> > > Per: [EMAIL PROTECTED]
> > > 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:
>[EMAIL PROTECTED]
> > > 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.
