AW: AW: Group by problem for Storagereports

Thanks for that trick - it works for me.
I made 6 new Domains - for each servercategory.

My Result:

tsm: ADSM>select cast(sum (filespaces.capacity/1024) as decimal(8,2)) as
"Instal
led GB" ,cast(sum(filespaces.capacity*pct_util/102400) as decimal(8,2)) as
"Used
 GB" , domains.description from nodes,filespaces,domains where
nodes.node_name=f
ilespaces.node_name and substr(nodes.contact,1,1)=domains.DOMAIN_name group
by d
omains.description

Installed GB        Used GB     DESCRIPTION
------------     ----------     ------------------
     2395.26        1018.27     Appl
      427.12         181.80     Database
     1809.32         890.50     File
     1659.71         379.36     Infrastruktur
      821.53         266.20     Mail
      354.38         135.46     SAP

(in the Serverdescription i use a number as the first letter for each 
category - i.e. "4, Contact,... " 4 means fileserver)

Regards 
Stefan Holzwarth


tsm: ADSM>
-----Ursprüngliche Nachricht-----
Von: Zlatko Krastev [mailto:acit AT ATTGLOBAL DOT NET]
Gesendet: Freitag, 15. November 2002 01:43
An: ADSM-L AT VM.MARIST DOT EDU
Betreff: Re: AW: Group by problem for Storagereports


Look at my reply on thread "SQL query - GROUP on derived value?" from 
25.09.2001.
There is a workaround.
 
Zlatko Krastev
IT Consultant
 
 
 
 
 
 
Stefan Holzwarth <stefan.holzwarth AT ADAC DOT DE>
Sent by: "ADSM: Dist Stor Manager" <ADSM-L AT VM.MARIST DOT EDU>
24.10.2002 18:13
Please respond to "ADSM: Dist Stor Manager"
 
 
        To:     ADSM-L AT VM.MARIST DOT EDU
        cc: 
        Subject:        AW: Group by problem for Storagereports
 
 
Group by nodes.contact works, but
groups by the whole string not the first letter.
Regards,
Stefan Holzwarth
 
 
-----Ursprüngliche Nachricht-----
Von: Tomás Hrouda [mailto:throuda AT HTD DOT CZ]
Gesendet: Donnerstag, 24. Oktober 2002 16:12
An: ADSM-L AT VM.MARIST DOT EDU
Betreff: Re: Group by problem for Storagereports
 
 
Did you try to use group by nodes.contact?
I tried you command (only without section  > filespace_name like '%\c$' < 
-
it didn't work)
 
select substr(nodes.contact,1,1) as SERVERTYP,sum 
(filespaces.capacity),sum
(filespaces.capacity*pct_util/100) from nodes,filespaces where
nodes.node_name=filespaces.node_name group by nodes.contact
 
gives this report
 
 SERVERTYP:
Unnamed[2]: 20024.3
Unnamed[3]: 16880.92
 
Is it OK?
Hope this helps
 
Tom
 
 
-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU]On Behalf Of
Stefan Holzwarth
Sent: Thursday, October 24, 2002 2:19 PM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Group by problem for Storagereports
 
 
Hello,
 
i tried to realize some storage reports about out NT servers with the
following select:
 
select substr(nodes.contact,1,1) as SERVERTYP ,sum (filespaces.capacity), 
-
      sum (filespaces.capacity*pct_util/100) from nodes,filespaces
where filespace_name like '%\c$' and nodes.node_name=filespaces.node_name
group by SERVERTYP
 
But:
===
ANR2940E The reference 'SERVERTYP' is an unknown SQL column name.
                                                            |
         ......................................................V........
         c$' and nodes.node_name=filespaces.node_name group by SERVERTYP
 
 
Any ideas who to group by the first letter of the description?
 
(I use the first letter for asigning the Server to some goups like mail,
application, file,.....)
 
Kind regards,
 
Stefan Holzwarth
 
----------------------------------------------------------------------------
--
Stefan Holzwarth
ADAC e.V. (Informationsverarbeitung - Systemtechnik - Basisdienste)
Am Westpark 8, 81373 M|nchen, Tel.: (089) 7676-5212, Fax: (089) 76768924
mailto:stefan.holzwarth AT adac DOT de