SQL script problem with inner SQL query expression

[Espelage]

Hello fans,

I have got a SQL script that shows me MAXscratch und used volumes per storage pool - it

works fine:



<TABLE BORDER=0 ALIGN=CENTER WIDTH=85%><TR><TD><font class="pn-sub">Code:</font><HR></TD></TR><TR><TD><FONT class="pn-sub"><PRE> select vol.stgpool_name as "Storage Pool", -

count(*) as "Volumes used" ,-

(select stgpools.maxscratch -

from stgpools stgpools -

where stgpools.devclass like '%LTO%' -

and stgpools.stgpool_name=vol.stgpool_name) -

as "Max. Scratch" -

from volumes vol -

where vol.devclass_name like '%LTO%' -

group by vol.stgpool_name </PRE></FONT></TD></TR><TR><TD><HR></TD></TR></TABLE>



I want to add a column that shows me the available space on filling volumes in these

storage pools, so I added another inner SQL query expression:



<TABLE BORDER=0 ALIGN=CENTER WIDTH=85%><TR><TD><font class="pn-sub">Code:</font><HR></TD></TR><TR><TD><FONT class="pn-sub"><PRE> select vol.stgpool_name as "Storage Pool", -

count(*) as "Volumes used", -

(select stgpools.maxscratch -

from stgpools stgpools -

where stgpools.devclass like '%LTO%' -

and stgpools.stgpool_name=vol.stgpool_name) -

as "Max. Scratch", -

(select (sum((100-vol1.pct_utilized)*vol1.EST_CAPACITY_MB)/102400) -

from volumes vol1, libvolumes libvols -

where vol1.stgpool_name=vol.stgpool_name -

and vol1.status='FILLING' -

and vol1.volume_name=libvols.volume_name -

group by vol1.stgpool_name) as "Free GB" -

from volumes vol -

where vol.devclass_name like '%LTO%' -

group by vol.stgpool_name </PRE></FONT></TD></TR><TR><TD><HR></TD></TR></TABLE>



This gives me the free GB of the FIRST storage pool in ALL lines - that's not what I want.



I tried out different variations - without effect.



Any ideas?



Thank you



Gernot

[email protected]