Eric: nope. The sub-select failed (rc=100) so it propagated as an empty
set/column for the top select. Not sure how the SQL around that.
As an aside, I suspect your SQL would fail if you had multiple StgPools with
Containers since the sub-select would return multiple PCT_UTILIZED rows. (you
might get multiple columns, tho') Now, I'm sure sum(pct_utilized) gives false
information so you probably need to do a similar calculation as you did with
Database.
------> signature = 8 lines follows <--------
Neil Duffee, Joe Sysprog, uOttawa, Ont, Canada
Telephone: 1 613 562 5800 x4585
mailto: NDuffee of uOttawa.ca http:/ /aix1.uOttawa.ca/ ~nduffee
"How *do* you plan for something like that?" Guardian Bob, Reboot
"For every action, there is an equal and opposite criticism."
"Systems Programming: Guilty, until proven innocent" John Norgauer 2004
"Schrodinger's backup: The condition of any backup is unknown until a restore
is attempted." John McKown 2015
-----Original Message-----
From: Loon, Eric van (ITOP NS) - KLM <eri...@kl...com>
Sent: March 30, 2020 5:31 AM
Subject: Re: Combining SQL queries
Although Tom's SQL statement works very well, there seems to be something weird
in the way the WHERE statement is handled. This is the output on a server with
a directory container pool:
select * from (select cast(sum(used_db_space_mb/tot_file_system_mb*100) as
decimal(3,1)) as "Database" from db) as database, (select pct_utilized as
"Containerpool" from stgpools where stgpool_name like 'CONTAINER%') as stgpool
Database Containerpool
--------- --------------
6.9 30.3
And now on a server without one:
select * from (select cast(sum(used_db_space_mb/tot_file_system_mb*100) as
decimal(3,1)) as "Database" from db) as database, (select pct_utilized as
"Containerpool" from stgpools where stgpool_name like 'CONTAINER%') as stgpool
ANR2034E SELECT: No match found using this criteria.
ANS8001I Return code 11.
So, it looks like the stgpool_name like 'CONTAINER%' statement is also applied
to the first SQL statement. That's weird, right?
Eric van Loon, Air France/KLM Storage & Backup
-----Original Message-----
From: ADSM: Dist Stor Manager <ADSM-L@VM.MARIST.EDU> On Behalf Of Tommaso
Bollini
Sent: donderdag 26 maart 2020 17:34
To: ADSM-L@VM.MARIST.EDU
Subject: R: [ADSM-L] Combining SQL queries
I need to use different syntax from the one of Eric, like:
select * from (select count(*) as "Number_Of_Failed" from events where
scheduled_start>current_timestamp-24 hours and status='Failed') as failed,
(select count(*) as "Number_Of_Missed" from events where
scheduled_start>current_timestamp-24 hours and status='Missed') as missed
since I've got a 'ANS8001I Return code 28'
-----Messaggio originale-----
Da: ADSM: Dist Stor Manager <ADSM-L@VM.MARIST.EDU> Per conto di Skylar Thompson
Inviato: giovedì 26 marzo 2020 17:14
A: ADSM-L@VM.MARIST.EDU
Oggetto: Re: [ADSM-L] Combining SQL queries
You can combine these two queries by executing them as subqueries:
select
*
from (
select count(*) as "Number_Of_Failed" from events where
scheduled_start>current_timestamp-24 hours and status='Failed'
) failed,
(
select count(*) as "Number_Of_Missed" from events where
scheduled_start>current_timestamp-24 hours and status='Missed'
) missed
;
On Thu, Mar 26, 2020 at 03:51:23PM +0000, Loon, Eric van (ITOP NS) - KLM wrote:
> I have two SQL queries:
>
> select count(*) as "Number_Of_Failed" from events where
> scheduled_start>current_timestamp-24 hours and status='Failed'
> select count(*) as "Number_Of_Missed" from events where
> scheduled_start>current_timestamp-24 hours and status='Missed'
>
> Is it somehow possible to combine them into one single query? I know I can
> use and status='Failed' or status='Missed', but I would like to be able to
> see how many are missed and how many are failed separately. My aim is to see
> if I can use one single line (with multiple columns) to report this in SPOC.
