Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a list of distinct areas, and would like to track statistics for those districts in a SQL statement. I currently have a setup that runs some summary information, but it doesn't include the whole list.

What I'd like to do is Select a distinct array of values from 1 table, then run SQL for each of those values against a few other tables and populate a third table. I've done a fair amount of Googling, and see that I can loop using "LOOP" or feed in the distinct values via a CURSOR.

Here's my SQL I'm using now, but if there isn't a dispatchgroup that is in the current outages table, it doesn't show statistics for that dispatch group.

 select a.DISPATCHGROUP, a.WEATHER, SUM(a.NUM_CUST) as out_cust, count(a.eventnum) as events,
(select count(*) from v_outcall_rpt b, v_current_outages_rpt c where b.isopen ='T' and b.dgroup = a.dispatchgroup )
as calls, 
(select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup  and c.event_type_code = 'TOEL') 
as ISOLATED, 
(select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup and (c.event_type_code = 'TOTO' or c.event_type_code = 'TOTU' or c.event_type_code = 'TOVT' or c.event_type_code = 'TOPM')) 
as TSTATION, 
(select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup and (c.event_type_code = 'TOFS' or c.event_type_code = 'TOSC' or c.event_type_code = 'TORL' or c.event_type_code = 'TOLC' or c.event_type_code = 'TOSW'))
as lateral,
(select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup and (c.event_type_code = 'TOCR' or c.event_type_code = 'TOSB' or c.event_type_code = 'TOTL' )) 
as mainline, 
(select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup and (c.event_type_code = 'TOEE' )) 
as emergency, 
(select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup and (c.event_type_code = 'TOEC' )) 
as cleanup, 
(select count(*) from v_outage_duration_rpt where substr(restore_dts,1,8) = (select SUBSTR(max(view_dts),1,8) from v_current_outages_rpt) and a.dispatchgroup = dgroup) as restored, 
(select count(*) from v_outcall_rpt where substr(offtime,1,8) = (select SUBSTR(max(view_dts),1,8) from v_current_outages_rpt) and a.dispatchgroup = dgroup) as callstoday 
from v_current_outages_rpt a where weather is not null group by a.DISPATCHGROUP, WEATHER;

When I try adding the select distinct dgroup from MV_WE_AGENCY_LOOKUP as dispatchgroup it errors out with single query returns more than one row.

Ideally what i'd like to happen is first I select the distinct dgroups from the first table, then loop through the SQL and output the statistics. Here's my stab at the psudocode.

FOR EACH DGROUP IN (select distinct dgroup from MV_WE_AGENCY_LOOKUP) 

LOOP
     a.WEATHER, SUM(a.NUM_CUST) as out_cust, count(a.eventnum) as events,
(select count(*) from v_outcall_rpt b, v_current_outages_rpt c where b.isopen ='T' and b.dgroup = %dgroup )
as calls, 
(select count(*) from v_current_outages_rpt c where c.dispatchgroup = %dgroup  and c.event_type_code = 'TOEL') 
as ISOLATED, 
(select count(*) from v_current_outages_rpt c where c.dispatchgroup = %dgroup and (c.event_type_code = 'TOTO' or c.event_type_code = 'TOTU' or c.event_type_code = 'TOVT' or c.event_type_code = 'TOPM')) 
as TSTATION, 
(select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup and (c.event_type_code = 'TOFS' or c.event_type_code = 'TOSC' or c.event_type_code = 'TORL' or c.event_type_code = 'TOLC' or c.event_type_code = 'TOSW'))
as lateral,
(select count(*) from v_current_outages_rpt c where c.dispatchgroup = a.dispatchgroup and (c.event_type_code = 'TOCR' or c.event_type_code = 'TOSB' or c.event_type_code = 'TOTL' )) 
as mainline, 
(select count(*) from v_current_outages_rpt c where c.dispatchgroup = %dgroup and (c.event_type_code = 'TOEE' )) 
as emergency, 
(select count(*) from v_current_outages_rpt c where c.dispatchgroup = %dgroup and (c.event_type_code = 'TOEC' )) 
as cleanup, 
(select count(*) from v_outage_duration_rpt where substr(restore_dts,1,8) = (select SUBSTR(max(view_dts),1,8) from v_current_outages_rpt) and %dgroup = dgroup) as restored, 
(select count(*) from v_outcall_rpt where substr(offtime,1,8) = (select SUBSTR(max(view_dts),1,8) from v_current_outages_rpt) and %dgroup = dgroup) as callstoday 
from v_current_outages_rpt a where weather is not null group by dgroup, WEATHER;
END LOOP
share|improve this question
    
Your question isn't clear. Where are you trying to add the select distinct dgroup from MV_WE_AGENCY_LOOKUP as dispatchgroup query? –  Phil Sep 18 '13 at 22:51
    
Sorry for the late reply, yes. I am trying to loop through the values of the select distinct when running the bottom half of the query. –  Kasey Sep 20 '13 at 18:33

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.