Re: SQL Select statement

Hello John,

          I use this script to get a list of tapes to recover a server when
we do our Business Recovery testing.

     Take care,
         Al


Name           Line       Command

               Number
----------     ------
------------------------------------------------------------
BRSLIST2       1          /* provides a list of tape needed to restore a
server*/
               2          /* USAGE: 'RUN BRSLIST nodename' */

               10         select distinct
volume_name,node_name,stgpool_name,filespac-
                           e_name from volumeusage where
node_name=upper('$1') order
                           by node_name,stgpool_name,volume_name


-----Original Message-----
From: John Stephens [mailto:jws AT STEPHENS DOT NET]
Sent: Thursday, January 22, 2004 12:30 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: SQL Select statement


I have a select statement that will display part of what I want but I cant
seem to make it completely work.
Here is the statement and the output,

select node_name as Node,count(distinct volume_name) as "Tape Pool
Media",count(distinct volume_name) as "Copy Pool Media" from volumeusage
where stgpool_name='TAPEPOOL' or stgpool_name='COPYPOOL'  group by node_name


NODE                   Tape Pool Media     Copy Pool Media
------------------     ---------------     ---------------
411                                  1                   1
5X                                   2                   2
6X                                   1                   1



I would like to have the select display the volumes from the tapepool and
the copypool locations for each node.
How can you build a select statement that will do this?

Thanks for any help


JWS