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@;ATTGLOBAL.NET]
Gesendet: Freitag, 15. November 2002 01:43
An: [EMAIL PROTECTED]
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 <[EMAIL PROTECTED]>
Sent by: "ADSM: Dist Stor Manager" <[EMAIL PROTECTED]>
24.10.2002 18:13
Please respond to "ADSM: Dist Stor Manager"
To: [EMAIL PROTECTED]
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@;HTD.CZ]
Gesendet: Donnerstag, 24. Oktober 2002 16:12
An: [EMAIL PROTECTED]
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@;VM.MARIST.EDU]On Behalf Of
Stefan Holzwarth
Sent: Thursday, October 24, 2002 2:19 PM
To: [EMAIL PROTECTED]
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@;adac.de
