On Monday, Jun 21, 2004, at 06:04 Australia/Sydney, Benhayoune Khalid
wrote:
Hi all,
The following SQL gives me a wrong result for the column NB_VOLUMES :
tsm: TSM>select stgpool_name,maxscratch,est_capacity_mb,pct_utilized, -
cont> (select count(*) from volumes where
stgpool_name=stgpools.stgpool_name) as NB_VOLUMES -
cont> from stgpools -
cont> where devclass <> 'DISK' -
cont> order by stgpool_name
STGPOOL_NAME MAXSCRATCH EST_CAPACITY_MB
PCT_UTILIZED
NB_VOLUMES
------------------ ----------- --------------------
------------
-----------
ARCH2004 15 5722020.0
4.2
2
COP2004 30 11444040.0
2.1
2
Not sure why the sub-query is not working, but an ordinary inner join
seems to work:
tsm: FRED>select stgpools.stgpool_name,stgpools.maxscratch,count(*) as
nb_volumes -
cont> from stgpools, volumes -
cont> where stgpools.stgpool_name = volumes.stgpool_name and devclass
<>'DISK' -
cont> group by stgpools.stgpool_name, stgpools.maxscratch -
cont> order by stgpools.stgpool_name
ANR2963W This SQL query may produce a very large result table, or may
require a significant amount of time to compute.
Do you wish to proceed? (Yes (Y)/No (N)) y
STGPOOL_NAME MAXSCRATCH NB_VOLUMES
------------------ ----------- -----------
OFFSITE 1000 306
TAPE1 500 92
TAPE2 500 69
TAPE3 500 81
Cheers,
--
Paul Ripke
Unix/OpenVMS/TSM/DBA
I love deadlines. I like the whooshing sound they make as they fly by.
-- Douglas Adams
|