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
