# Query/View Optimisation for Oracle8i

**725270**Sep 30, 2009 6:09 PM

Hi,

I am hoping somebody here can help.

I currently have an Oracle 8i Database View/Query that is quite large and ugly and very time consuming to run and pulls information from multiple tables. I am after some tips to help me streamline it.

Would I be better to split this query up into multiple views? Is there any code I can change to help it run better.

Any help is appreciated :)

select "LINE_GOR","GROUPNAME","SUB","HUB","LINECODE","FROM_","TO_","LENGTH (m)","DINIS_GRP_CURRENT","% DINIS_GRP_CURRENT",

"DINIS_SEGMENT_CURRENT","GRP_SD50","GRP_SN50","GRP_WD50","GRP_WN50","SEG_SD","SEG_SN","SEG_WD","SEG_WN","SD_CONT_RAT",

"SN_CONT_RAT","WD_CONT_RAT","WN_CONT_RAT","SDLF","WDLF","SCRF","WCRF","SD_RATING","SN_RATING","WD_RATING","WN_RATING","SD_UTILISATION",

"SN_UTILISATION","WD_UTILISATION","WN_UTILISATION","WORST_UTILISATION","TMU","SD_GRP_RATING","SN_GRP_RATING","WD_GRP_RATING","WN_GRP_RATING",

"SD_GRP_VOLT_REG","SN_GRP_VOLT_REG","WD_GRP_VOLT_REG","WN_GRP_VOLT_REG","WORST_VOLT_REG","DINIS Power Loss (kW)","LOADFLOW_DATE",

"LOADFLOW_NOTES","PROJECT_NO","PROJECT_TYPE","POW","COMMENTS","COMMENTS_DATE","GRP_SD10","GRP_SN10","GRP_WD10","GRP_WN10"

from

(--------------------------------------------------------------------------------

-- CALCULATIONS

select groupname, sub, hub, line_gor, linecode, from_, to_, "LENGTH (m)", dinis_grp_current,

round("% DINIS_GRP_CURRENT",2) "% DINIS_GRP_CURRENT", dinis_segment_current,

grp_sd grp_sd50, grp_sn grp_sn50, grp_wd grp_wd50, grp_wn grp_wn50, seg_sd, seg_sn, seg_wd, seg_wn,

sd_cont_rat, sn_cont_rat, wd_cont_rat, wn_cont_rat,

sdlf, wdlf, scrf, wcrf,

round(sd_cont_rat*scrf,0) sd_rating, -- Segment Cyclic Ratings ie. Continuous Rating * Group Cyclic Rating Factor

round(sn_cont_rat*scrf,0) sn_rating,

round(wd_cont_rat*wcrf,0) wd_rating,

round(wn_cont_rat*wcrf,0) wn_rating,

round ((case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then 0 -- Segment Utilisation

else seg_sd/(sd_cont_rat*scrf)*100 end) ,0) sd_utilisation, -- If SD Forecast for a feeder/group is 0, a DIINIS Loadflow is not run.

-- The % DINIS Group Current is Null and the Group Current is 0, and

round ((case when "% DINIS_GRP_CURRENT" is null and grp_sn = 0 then 0 -- only Cnductor and Node data has been imported.

else seg_sn/(sn_cont_rat*scrf)*100 end) ,0) sn_utilisation, -- In this case the Utilisations are set to 0.

round ((case when "% DINIS_GRP_CURRENT" is null and grp_wd = 0 then 0 -- Utilisations are Segment Current / Segment Cyclic Rating

else seg_wd/(wd_cont_rat*wcrf)*100 end) ,0) wd_utilisation,

round ((case when "% DINIS_GRP_CURRENT" is null and grp_wn = 0 then 0

else seg_wn/(wn_cont_rat*wcrf)*100 end) ,0) wn_utilisation,

greatest(

round ((case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then 0 -- Segment Worst Utilisation

else seg_sd/(sd_cont_rat*scrf)*100 end) ,0),

round ((case when "% DINIS_GRP_CURRENT" is null and grp_sn = 0 then 0

else seg_sn/(sn_cont_rat*scrf)*100 end) ,0),

round ((case when "% DINIS_GRP_CURRENT" is null and grp_wd = 0 then 0

else seg_wd/(wd_cont_rat*wcrf)*100 end) ,0),

round ((case when "% DINIS_GRP_CURRENT" is null and grp_wn = 0 then 0

else seg_wn/(wn_cont_rat*wcrf)*100 end) ,0) ) worst_utilisation,

tmu,

round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then sd_cont_rat*scrf -- Group Rating relative to Segemnt Utilisation

else (case when "% DINIS_GRP_CURRENT" = 0 then 9999 -- When the % DINIS Group Current is null and the Group Summer Day Load = 0

else (case when (sd_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999 -- ie. No Loadflow has been run, the Segment Cyclic Rating becomes the Group Rating

else (sd_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) sd_grp_rating, -- When the % DINIS Group Current = 0, the Group Rating = 9999

-- The Group Rating is capped at 9999

-- Group Ratings are Cyclic Rating * 100 / % DINIS Group Current

round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then sn_cont_rat*scrf

else (case when "% DINIS_GRP_CURRENT" = 0 then 9999

else (case when (sn_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999

else (sn_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) sn_grp_rating,

round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then wd_cont_rat*wcrf

else (case when "% DINIS_GRP_CURRENT" = 0 then 9999

else (case when (wd_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999

else (wd_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) wd_grp_rating,

round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then wn_cont_rat*wcrf

else (case when "% DINIS_GRP_CURRENT" = 0 then 9999

else (case when (wn_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999

else (wn_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) wn_grp_rating,

round(dinis_grp_volt_reg/dinis_grp_current*grp_sd,1) sd_grp_volt_reg,

round(dinis_grp_volt_reg/dinis_grp_current*grp_sn,1) sn_grp_volt_reg,

round(dinis_grp_volt_reg/dinis_grp_current*grp_wd,1) wd_grp_volt_reg,

round(dinis_grp_volt_reg/dinis_grp_current*grp_wn,1) wn_grp_volt_reg,

greatest ( round(dinis_grp_volt_reg/dinis_grp_current*grp_sd,1), round(dinis_grp_volt_reg/dinis_grp_current*grp_sn,1),

round(dinis_grp_volt_reg/dinis_grp_current*grp_wd,1), round(dinis_grp_volt_reg/dinis_grp_current*grp_wn,1) ) worst_volt_reg,

dinis_real_power_loss "DINIS Power Loss (kW)", daterun loadflow_date, errornotes loadflow_notes,

project_no, project_type, pow,comments, last_update_time comments_date,

grp_sd10, grp_sn10, grp_wd10, grp_wn10

from

(--------------------------------------------------------------------------------

-- DATA

-- Tables Structure

-- t1 -

-- t2 -

-- t3 - = t6 -

-- t4 -

-- t5 -

-- t7 - = DATA

-- t8 -

-- t9 -

-- t10 -

-- t11 -

select distinct t6.groupname, t7.sub, t7.hub, t6.line_gor, t6.linecode, t6.from_, t6.to_, t6."LENGTH (m)",

dinis_grp_current, t6."% DINIS_GRP_CURRENT", t6.dinis_segment_current,

t6.grp_sd, t6.grp_sn, t6.grp_wd, t6.grp_wn,t6.grp_sd10, t6.grp_sn10, t6.grp_wd10, t6.grp_wn10,

t6.seg_sd, t6.seg_sn, t6.seg_wd, t6.seg_wn,

t6.sd_cont_rat, t6.sn_cont_rat, t6.wd_cont_rat, t6.wn_cont_rat,

t6.sdlf, t6.wdlf, t6.scrf, t6.wcrf, t7.tmu, t8.errornotes, t8.daterun, t9.dinis_grp_volt_reg,

t10.dinis_real_power_loss, t11.project_no, t11.project_type, t11.pow, t11.comments, t11.last_update_time

from -- Tables 6, 7, 8, 9 , 10 & 11

( -- Table 6, Join of Tables 1, 2, 3, 4 & 5

select distinct t1.groupname, t1.line_gor, t1.linecode, t1.userreference1 from_, t2.userreference1 to_, t1.nodetext3 project_s, t2.nodetext3 project_r, t1.length "LENGTH (m)",

t3.group_current dinis_grp_current,

(case when (t3.group_current is null or t3.group_current = 0) then null -- See notes in Table 3

else t1.current_in/t3.group_current*100 end)"% DINIS_GRP_CURRENT",

t1.current_in dinis_segment_current,

t4.grp_sd, t4.grp_sn, t4.grp_wd, t4.grp_wn, grp_sd10, grp_sn10, grp_wd10,grp_wn10,

(case when (t3.group_current is null or t3.group_current = 0) then null

else round(t1.current_in/t3.group_current*t4.grp_sd,0) end) seg_sd,

(case when (t3.group_current is null or t3.group_current = 0) then null

else round(t1.current_in/t3.group_current*t4.grp_sn,0) end) seg_sn,

(case when (t3.group_current is null or t3.group_current = 0) then null

else round(t1.current_in/t3.group_current*t4.grp_wd,0) end) seg_wd,

(case when (t3.group_current is null or t3.group_current = 0) then null

else round(t1.current_in/t3.group_current*t4.grp_wn,0) end) seg_wn,

t5.sd_cont_rat, t5.sn_cont_rat, t5.wd_cont_rat, t5.wn_cont_rat,

t4.sdlf sdlf, t4.wdlf wdlf,

(case when (t1.linecode like '%UG%' or t1.linecode like '%CBD%') then -- OH cable Cyclis Rating Factor = 1

(select crf

from dlf_to_crf

where min_dlf <= t4.sdlf and max_dlf > t4.sdlf)

else 1 end) scrf,

(case when (t1.linecode like '%UG%' or t1.linecode like '%CBD%') then -- UG cable Cyclic Rating Factor Look Up

(select crf -- from DLF_TO_CRT table

from dlf_to_crf

where min_dlf <= t4.wdlf and max_dlf > t4.wdlf) -- DLF determined in Table 4

else 1 end) wcrf

from

( -- Table 1

-- Line Results converted to kVar, kW and Amps

-- Line Results joined to Node Results on "Sendingnodegor"

select distinct

lr.groupname, lr.gor line_gor, round(lr.userspecifiedlinelength,0) length, lr.linecode,

round((lr.reactivepowerin*power(10,6))/1000,2) Reactive_Power_in_kVar,

round((lr.reactivepowerout*power(10,6))/1000,2) Reactive_Power_out_kVar,

round((lr.reactivepowerloss*power(10,6))/1000,2) Reactive_Power_Loss_kVar,

round((lr.realpowerin*power(10,6))/1000,2)Real_Power_in_kW,

round((lr.realpowerout*power(10,6))/1000,2) Real_Power_Out_kW,

round((lr.realpowerloss*power(10,6))/1000,2) Real_Power_Loss_kW,

round (greatest (lr.currentin, lr.currentout) * 5248.64, 2) current_in, -- Selects highest current as "Current In"

round (least (lr.currentin, lr.currentout) * 5248.64, 2) current_out, -- Selects lowest current as "Current Out"

lr.sendingnodegor,nr.userreference1, nr.nodetext3,

lr.receivingnodegor

from autoloadflow_lineresults lr,

autoloadflow_noderesults nr

where lr.sendingnodegor = nr.gor (+) and

not lr.linecode = '11kV_BUSBAR' and -- Excludes Busbar inside Substation sites

lr.groupname like '%' ) t1, -- Table 1

( -- Table 2

-- Line Results GOR and Receiving Node Gor only

-- Line Results joined to Node Results on "Receivingnodegor"

select distinct

lr.groupname, lr.gor line_gor,

lr.receivingnodegor, nr.userreference1, nr.nodetext3

from autoloadflow_lineresults lr,

autoloadflow_noderesults nr

where lr.receivingnodegor = nr.gor (+) and

not lr.linecode = '11kV_BUSBAR' and -- Excludes Busbar inside Substation sites

lr.groupname like '%' ) t2, -- Table 2

( -- Table 3

-- Group Current in DINIS Loadflow

-- For radial feeders, selects the highest current found in the loadflow Line Results

-- For group/mesh feeders, selects the sum of Group Load values from the AUTOLOADFLOW_GROUPING table

-- Note, the results rely the AUTOLOADFLOW_GROUPING table being up-to-date

select groupname, max (round (greatest (lr.currentin, lr.currentout) * 5248.64, 2)) group_current

from distplan.autoloadflow_lineresults lr

where not groupname like '___G__' and not groupname like '______G__%' -- radial feeders -- Excluding Groups

and not groupname in ('APLLYT6','APLLYT10', 'APLLYT13', 'CTXLYT7', 'CTXLYT16', 'BDB6', 'BDBBRW3', -- Excluding parallel cables

'BDBBRW8', 'VSP1')

group by groupname -- If a feeder has "0" load, the Line Results currents will be Null ie. Tie feeders

union

select groupname, sum(load) load -- Sums multiple loads for groups/mesh feeders

from autoloadflow_grouping

where groupname like '___G__' or groupname like '______G__%' -- Groups/Mesh feeders

or groupname in ('APLLYT6','APLLYT10', 'APLLYT13', 'CTXLYT7', 'CTXLYT16', 'BDB6', 'BDBBRW3', -- Parallel cables

'BDBBRW8', 'VSP1')

group by groupname

order by groupname ) t3, -- Table 3

( --Table 4

-- Groupname GRP and SD, SN, WD, WN Group Forecast Loads, Group Summer DLF, Group Wiinter DLF

-- from the Transaction Viewer for the most recent transactions before taday excluding GROWTH RATE transactions

select t4b.grp,

sum(t4a.grp_sd) grp_sd,

sum(t4a.grp_sn) grp_sn,

sum(t4a.grp_wd) grp_wd,

sum(t4a.grp_wn) grp_wn,

sum(t4a.grp_sd10) grp_sd10,

sum(t4a.grp_sn10) grp_sn10,

sum(t4a.grp_wd10) grp_wd10,

sum(t4a.grp_wn10) grp_wn10,

round( avg(t4b.SDLF) ,4) SDLF,

round( avg(t4b.WDLF) ,4) WDLF

from

( -- Table 4a, All Forecast Events from the Forecast, Partitioned and Sorted with most recent RN = 1

select lmp,

event,

dt,

round(load_50poe_sd,0) grp_sd, -- Rounds loads

round(load_50poe_sn,0) grp_sn,

round(load_50poe_wd,0) grp_wd,

round(load_50poe_wn,0) grp_wn,

round(load_10poe_sd,0) grp_sd10, -- Rounds loads

round(load_10poe_sn,0) grp_sn10,

round(load_10poe_wd,0) grp_wd10,

round(load_10poe_wn,0) grp_wn10,

row_number() over (partition by lmp order by dt desc, load_50poe_sd) rn

-- Partition data by LMP, Order by DT descending and 50POE

-- Insert row numbers for each partition

from forecast

where dt < sysdate and -- Where the transaction date is before today's date

not event = 'GROWTH RATE' and -- Ignore Growth Rate Transactions in the Forecast

not event = 'THERMAL RATING' and -- Ignore Thermal rating Transactions in the Forecast

committed = 'YES' ) t4a, -- Table 4a -- Only committed transactions

( -- Tabale 4b, LMP, GRP, Summer DLF and Winter DLF from Feeder table

select lmp,

grp,

daily_load_factor_summer sdlf,

daily_load_factor_winter wdlf

from feeders_new ) t4b -- Table 4b

where t4a.lmp = t4b.lmp and

t4a.rn = 1

group by t4b.grp ) t4, -- Table 4

( -- Table 5

-- Continuous Cable Ratings

select linecode, round(sd,0) sd_cont_rat, round(sn,0) sn_cont_rat, round(wd,0) wd_cont_rat, round(wn,0) wn_cont_rat

from ratings_oh_cyclic

union

select linecode, summer sd_cont_rat, summer sn_cont_rat, winter wd_cont_rat, winter wn_cont_rat

from ratings_ug_continuous

order by linecode ) t5 -- Table 5

where t1.line_gor = t2.line_gor and -- Join Tables 1, 2, 3, 4 & 5

t1.groupname = t3.groupname (+) and

t1.groupname = t4.grp (+) and

t1.linecode = t5.linecode

order by groupname

) t6, -- Table 6, (Tables 1, 2, 3, 4 & 5 )

( -- Table 7

-- Grp, Sub, Hub and TMU Data

select t7a.grp, t7b.sub_id, t7c.alpha sub, t7b.hub, t7a.tmu

from

(select distinct grp, avg(target_maximum_utilisation)*100 tmu

from feeders_new

where grp in (select groupname from autoloadflow_lineresults) group by grp) t7a, -- Table 7a

(select distinct grp, hub, sub_id

from feeders_new where grp in (select groupname from autoloadflow_lineresults) ) t7b, -- Table 7b

(select id, alpha from substations ) t7c -- Table 7c

where t7a.grp = t7b.grp and

t7b.sub_id = t7c.id ) t7, -- Table 7

( -- Table 8

-- DINIS Loadflow Summary Results

select groupname, errornotes, daterun

from autoloadflow_summaryresults ) t8, -- Table 8

( -- Table 9

-- DINIS Loadflow Voltage Regulation

select groupname, ( max(voltagemagnitude)-min(voltagemagnitude) ) *100 dinis_grp_volt_reg

from autoloadflow_noderesults

group by groupname ) t9, -- Table 9

( -- Table 10

-- DINIS Group Power Loss

select groupname, round (sum((realpowerloss*100*power(10,6))/1000),2) dinis_real_power_loss

from autoloadflow_lineresults

group by groupname ) t10, -- Table 10

( -- Table 11

-- Autoloadflow_Limits_Projects

select project_no, group_name,

(case when (rating_limit = 'YES' and voltage_limit is null) then 'RATING'

when (rating_limit is null and voltage_limit = 'YES') then 'VOLTAGE'

when (rating_limit = 'YES' and voltage_limit = 'YES') then 'RATING and VOLTAGE'

when (rating_limit is null and voltage_limit is null) then null

else 'XXXXXXXX' end) project_type,

from_, to_, dinis_line_gor, comments, pow, last_update_time, date_completed

from autoloadflow_limits_projects

order by group_name ) t11 -- Table 11

where t6.groupname = t7.grp and -- Join Tables 6, 7, 8, 9, 10 & 11

t6.groupname = t8.groupname and

t6.groupname = t9.groupname and

t6.groupname = t10.groupname and

t6.line_gor = t11.dinis_line_gor (+)

)

)

where groupname like '%' and

hub like '%'

order by groupname, worst_utilisation desc

I am hoping somebody here can help.

I currently have an Oracle 8i Database View/Query that is quite large and ugly and very time consuming to run and pulls information from multiple tables. I am after some tips to help me streamline it.

Would I be better to split this query up into multiple views? Is there any code I can change to help it run better.

Any help is appreciated :)

select "LINE_GOR","GROUPNAME","SUB","HUB","LINECODE","FROM_","TO_","LENGTH (m)","DINIS_GRP_CURRENT","% DINIS_GRP_CURRENT",

"DINIS_SEGMENT_CURRENT","GRP_SD50","GRP_SN50","GRP_WD50","GRP_WN50","SEG_SD","SEG_SN","SEG_WD","SEG_WN","SD_CONT_RAT",

"SN_CONT_RAT","WD_CONT_RAT","WN_CONT_RAT","SDLF","WDLF","SCRF","WCRF","SD_RATING","SN_RATING","WD_RATING","WN_RATING","SD_UTILISATION",

"SN_UTILISATION","WD_UTILISATION","WN_UTILISATION","WORST_UTILISATION","TMU","SD_GRP_RATING","SN_GRP_RATING","WD_GRP_RATING","WN_GRP_RATING",

"SD_GRP_VOLT_REG","SN_GRP_VOLT_REG","WD_GRP_VOLT_REG","WN_GRP_VOLT_REG","WORST_VOLT_REG","DINIS Power Loss (kW)","LOADFLOW_DATE",

"LOADFLOW_NOTES","PROJECT_NO","PROJECT_TYPE","POW","COMMENTS","COMMENTS_DATE","GRP_SD10","GRP_SN10","GRP_WD10","GRP_WN10"

from

(--------------------------------------------------------------------------------

-- CALCULATIONS

select groupname, sub, hub, line_gor, linecode, from_, to_, "LENGTH (m)", dinis_grp_current,

round("% DINIS_GRP_CURRENT",2) "% DINIS_GRP_CURRENT", dinis_segment_current,

grp_sd grp_sd50, grp_sn grp_sn50, grp_wd grp_wd50, grp_wn grp_wn50, seg_sd, seg_sn, seg_wd, seg_wn,

sd_cont_rat, sn_cont_rat, wd_cont_rat, wn_cont_rat,

sdlf, wdlf, scrf, wcrf,

round(sd_cont_rat*scrf,0) sd_rating, -- Segment Cyclic Ratings ie. Continuous Rating * Group Cyclic Rating Factor

round(sn_cont_rat*scrf,0) sn_rating,

round(wd_cont_rat*wcrf,0) wd_rating,

round(wn_cont_rat*wcrf,0) wn_rating,

round ((case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then 0 -- Segment Utilisation

else seg_sd/(sd_cont_rat*scrf)*100 end) ,0) sd_utilisation, -- If SD Forecast for a feeder/group is 0, a DIINIS Loadflow is not run.

-- The % DINIS Group Current is Null and the Group Current is 0, and

round ((case when "% DINIS_GRP_CURRENT" is null and grp_sn = 0 then 0 -- only Cnductor and Node data has been imported.

else seg_sn/(sn_cont_rat*scrf)*100 end) ,0) sn_utilisation, -- In this case the Utilisations are set to 0.

round ((case when "% DINIS_GRP_CURRENT" is null and grp_wd = 0 then 0 -- Utilisations are Segment Current / Segment Cyclic Rating

else seg_wd/(wd_cont_rat*wcrf)*100 end) ,0) wd_utilisation,

round ((case when "% DINIS_GRP_CURRENT" is null and grp_wn = 0 then 0

else seg_wn/(wn_cont_rat*wcrf)*100 end) ,0) wn_utilisation,

greatest(

round ((case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then 0 -- Segment Worst Utilisation

else seg_sd/(sd_cont_rat*scrf)*100 end) ,0),

round ((case when "% DINIS_GRP_CURRENT" is null and grp_sn = 0 then 0

else seg_sn/(sn_cont_rat*scrf)*100 end) ,0),

round ((case when "% DINIS_GRP_CURRENT" is null and grp_wd = 0 then 0

else seg_wd/(wd_cont_rat*wcrf)*100 end) ,0),

round ((case when "% DINIS_GRP_CURRENT" is null and grp_wn = 0 then 0

else seg_wn/(wn_cont_rat*wcrf)*100 end) ,0) ) worst_utilisation,

tmu,

round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then sd_cont_rat*scrf -- Group Rating relative to Segemnt Utilisation

else (case when "% DINIS_GRP_CURRENT" = 0 then 9999 -- When the % DINIS Group Current is null and the Group Summer Day Load = 0

else (case when (sd_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999 -- ie. No Loadflow has been run, the Segment Cyclic Rating becomes the Group Rating

else (sd_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) sd_grp_rating, -- When the % DINIS Group Current = 0, the Group Rating = 9999

-- The Group Rating is capped at 9999

-- Group Ratings are Cyclic Rating * 100 / % DINIS Group Current

round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then sn_cont_rat*scrf

else (case when "% DINIS_GRP_CURRENT" = 0 then 9999

else (case when (sn_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999

else (sn_cont_rat*scrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) sn_grp_rating,

round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then wd_cont_rat*wcrf

else (case when "% DINIS_GRP_CURRENT" = 0 then 9999

else (case when (wd_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999

else (wd_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) wd_grp_rating,

round( (case when "% DINIS_GRP_CURRENT" is null and grp_sd = 0 then wn_cont_rat*wcrf

else (case when "% DINIS_GRP_CURRENT" = 0 then 9999

else (case when (wn_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" > 9999 then 9999

else (wn_cont_rat*wcrf*100)/"% DINIS_GRP_CURRENT" end) end) end) ,0) wn_grp_rating,

round(dinis_grp_volt_reg/dinis_grp_current*grp_sd,1) sd_grp_volt_reg,

round(dinis_grp_volt_reg/dinis_grp_current*grp_sn,1) sn_grp_volt_reg,

round(dinis_grp_volt_reg/dinis_grp_current*grp_wd,1) wd_grp_volt_reg,

round(dinis_grp_volt_reg/dinis_grp_current*grp_wn,1) wn_grp_volt_reg,

greatest ( round(dinis_grp_volt_reg/dinis_grp_current*grp_sd,1), round(dinis_grp_volt_reg/dinis_grp_current*grp_sn,1),

round(dinis_grp_volt_reg/dinis_grp_current*grp_wd,1), round(dinis_grp_volt_reg/dinis_grp_current*grp_wn,1) ) worst_volt_reg,

dinis_real_power_loss "DINIS Power Loss (kW)", daterun loadflow_date, errornotes loadflow_notes,

project_no, project_type, pow,comments, last_update_time comments_date,

grp_sd10, grp_sn10, grp_wd10, grp_wn10

from

(--------------------------------------------------------------------------------

-- DATA

-- Tables Structure

-- t1 -

-- t2 -

-- t3 - = t6 -

-- t4 -

-- t5 -

-- t7 - = DATA

-- t8 -

-- t9 -

-- t10 -

-- t11 -

select distinct t6.groupname, t7.sub, t7.hub, t6.line_gor, t6.linecode, t6.from_, t6.to_, t6."LENGTH (m)",

dinis_grp_current, t6."% DINIS_GRP_CURRENT", t6.dinis_segment_current,

t6.grp_sd, t6.grp_sn, t6.grp_wd, t6.grp_wn,t6.grp_sd10, t6.grp_sn10, t6.grp_wd10, t6.grp_wn10,

t6.seg_sd, t6.seg_sn, t6.seg_wd, t6.seg_wn,

t6.sd_cont_rat, t6.sn_cont_rat, t6.wd_cont_rat, t6.wn_cont_rat,

t6.sdlf, t6.wdlf, t6.scrf, t6.wcrf, t7.tmu, t8.errornotes, t8.daterun, t9.dinis_grp_volt_reg,

t10.dinis_real_power_loss, t11.project_no, t11.project_type, t11.pow, t11.comments, t11.last_update_time

from -- Tables 6, 7, 8, 9 , 10 & 11

( -- Table 6, Join of Tables 1, 2, 3, 4 & 5

select distinct t1.groupname, t1.line_gor, t1.linecode, t1.userreference1 from_, t2.userreference1 to_, t1.nodetext3 project_s, t2.nodetext3 project_r, t1.length "LENGTH (m)",

t3.group_current dinis_grp_current,

(case when (t3.group_current is null or t3.group_current = 0) then null -- See notes in Table 3

else t1.current_in/t3.group_current*100 end)"% DINIS_GRP_CURRENT",

t1.current_in dinis_segment_current,

t4.grp_sd, t4.grp_sn, t4.grp_wd, t4.grp_wn, grp_sd10, grp_sn10, grp_wd10,grp_wn10,

(case when (t3.group_current is null or t3.group_current = 0) then null

else round(t1.current_in/t3.group_current*t4.grp_sd,0) end) seg_sd,

(case when (t3.group_current is null or t3.group_current = 0) then null

else round(t1.current_in/t3.group_current*t4.grp_sn,0) end) seg_sn,

(case when (t3.group_current is null or t3.group_current = 0) then null

else round(t1.current_in/t3.group_current*t4.grp_wd,0) end) seg_wd,

(case when (t3.group_current is null or t3.group_current = 0) then null

else round(t1.current_in/t3.group_current*t4.grp_wn,0) end) seg_wn,

t5.sd_cont_rat, t5.sn_cont_rat, t5.wd_cont_rat, t5.wn_cont_rat,

t4.sdlf sdlf, t4.wdlf wdlf,

(case when (t1.linecode like '%UG%' or t1.linecode like '%CBD%') then -- OH cable Cyclis Rating Factor = 1

(select crf

from dlf_to_crf

where min_dlf <= t4.sdlf and max_dlf > t4.sdlf)

else 1 end) scrf,

(case when (t1.linecode like '%UG%' or t1.linecode like '%CBD%') then -- UG cable Cyclic Rating Factor Look Up

(select crf -- from DLF_TO_CRT table

from dlf_to_crf

where min_dlf <= t4.wdlf and max_dlf > t4.wdlf) -- DLF determined in Table 4

else 1 end) wcrf

from

( -- Table 1

-- Line Results converted to kVar, kW and Amps

-- Line Results joined to Node Results on "Sendingnodegor"

select distinct

lr.groupname, lr.gor line_gor, round(lr.userspecifiedlinelength,0) length, lr.linecode,

round((lr.reactivepowerin*power(10,6))/1000,2) Reactive_Power_in_kVar,

round((lr.reactivepowerout*power(10,6))/1000,2) Reactive_Power_out_kVar,

round((lr.reactivepowerloss*power(10,6))/1000,2) Reactive_Power_Loss_kVar,

round((lr.realpowerin*power(10,6))/1000,2)Real_Power_in_kW,

round((lr.realpowerout*power(10,6))/1000,2) Real_Power_Out_kW,

round((lr.realpowerloss*power(10,6))/1000,2) Real_Power_Loss_kW,

round (greatest (lr.currentin, lr.currentout) * 5248.64, 2) current_in, -- Selects highest current as "Current In"

round (least (lr.currentin, lr.currentout) * 5248.64, 2) current_out, -- Selects lowest current as "Current Out"

lr.sendingnodegor,nr.userreference1, nr.nodetext3,

lr.receivingnodegor

from autoloadflow_lineresults lr,

autoloadflow_noderesults nr

where lr.sendingnodegor = nr.gor (+) and

not lr.linecode = '11kV_BUSBAR' and -- Excludes Busbar inside Substation sites

lr.groupname like '%' ) t1, -- Table 1

( -- Table 2

-- Line Results GOR and Receiving Node Gor only

-- Line Results joined to Node Results on "Receivingnodegor"

select distinct

lr.groupname, lr.gor line_gor,

lr.receivingnodegor, nr.userreference1, nr.nodetext3

from autoloadflow_lineresults lr,

autoloadflow_noderesults nr

where lr.receivingnodegor = nr.gor (+) and

not lr.linecode = '11kV_BUSBAR' and -- Excludes Busbar inside Substation sites

lr.groupname like '%' ) t2, -- Table 2

( -- Table 3

-- Group Current in DINIS Loadflow

-- For radial feeders, selects the highest current found in the loadflow Line Results

-- For group/mesh feeders, selects the sum of Group Load values from the AUTOLOADFLOW_GROUPING table

-- Note, the results rely the AUTOLOADFLOW_GROUPING table being up-to-date

select groupname, max (round (greatest (lr.currentin, lr.currentout) * 5248.64, 2)) group_current

from distplan.autoloadflow_lineresults lr

where not groupname like '___G__' and not groupname like '______G__%' -- radial feeders -- Excluding Groups

and not groupname in ('APLLYT6','APLLYT10', 'APLLYT13', 'CTXLYT7', 'CTXLYT16', 'BDB6', 'BDBBRW3', -- Excluding parallel cables

'BDBBRW8', 'VSP1')

group by groupname -- If a feeder has "0" load, the Line Results currents will be Null ie. Tie feeders

union

select groupname, sum(load) load -- Sums multiple loads for groups/mesh feeders

from autoloadflow_grouping

where groupname like '___G__' or groupname like '______G__%' -- Groups/Mesh feeders

or groupname in ('APLLYT6','APLLYT10', 'APLLYT13', 'CTXLYT7', 'CTXLYT16', 'BDB6', 'BDBBRW3', -- Parallel cables

'BDBBRW8', 'VSP1')

group by groupname

order by groupname ) t3, -- Table 3

( --Table 4

-- Groupname GRP and SD, SN, WD, WN Group Forecast Loads, Group Summer DLF, Group Wiinter DLF

-- from the Transaction Viewer for the most recent transactions before taday excluding GROWTH RATE transactions

select t4b.grp,

sum(t4a.grp_sd) grp_sd,

sum(t4a.grp_sn) grp_sn,

sum(t4a.grp_wd) grp_wd,

sum(t4a.grp_wn) grp_wn,

sum(t4a.grp_sd10) grp_sd10,

sum(t4a.grp_sn10) grp_sn10,

sum(t4a.grp_wd10) grp_wd10,

sum(t4a.grp_wn10) grp_wn10,

round( avg(t4b.SDLF) ,4) SDLF,

round( avg(t4b.WDLF) ,4) WDLF

from

( -- Table 4a, All Forecast Events from the Forecast, Partitioned and Sorted with most recent RN = 1

select lmp,

event,

dt,

round(load_50poe_sd,0) grp_sd, -- Rounds loads

round(load_50poe_sn,0) grp_sn,

round(load_50poe_wd,0) grp_wd,

round(load_50poe_wn,0) grp_wn,

round(load_10poe_sd,0) grp_sd10, -- Rounds loads

round(load_10poe_sn,0) grp_sn10,

round(load_10poe_wd,0) grp_wd10,

round(load_10poe_wn,0) grp_wn10,

row_number() over (partition by lmp order by dt desc, load_50poe_sd) rn

-- Partition data by LMP, Order by DT descending and 50POE

-- Insert row numbers for each partition

from forecast

where dt < sysdate and -- Where the transaction date is before today's date

not event = 'GROWTH RATE' and -- Ignore Growth Rate Transactions in the Forecast

not event = 'THERMAL RATING' and -- Ignore Thermal rating Transactions in the Forecast

committed = 'YES' ) t4a, -- Table 4a -- Only committed transactions

( -- Tabale 4b, LMP, GRP, Summer DLF and Winter DLF from Feeder table

select lmp,

grp,

daily_load_factor_summer sdlf,

daily_load_factor_winter wdlf

from feeders_new ) t4b -- Table 4b

where t4a.lmp = t4b.lmp and

t4a.rn = 1

group by t4b.grp ) t4, -- Table 4

( -- Table 5

-- Continuous Cable Ratings

select linecode, round(sd,0) sd_cont_rat, round(sn,0) sn_cont_rat, round(wd,0) wd_cont_rat, round(wn,0) wn_cont_rat

from ratings_oh_cyclic

union

select linecode, summer sd_cont_rat, summer sn_cont_rat, winter wd_cont_rat, winter wn_cont_rat

from ratings_ug_continuous

order by linecode ) t5 -- Table 5

where t1.line_gor = t2.line_gor and -- Join Tables 1, 2, 3, 4 & 5

t1.groupname = t3.groupname (+) and

t1.groupname = t4.grp (+) and

t1.linecode = t5.linecode

order by groupname

) t6, -- Table 6, (Tables 1, 2, 3, 4 & 5 )

( -- Table 7

-- Grp, Sub, Hub and TMU Data

select t7a.grp, t7b.sub_id, t7c.alpha sub, t7b.hub, t7a.tmu

from

(select distinct grp, avg(target_maximum_utilisation)*100 tmu

from feeders_new

where grp in (select groupname from autoloadflow_lineresults) group by grp) t7a, -- Table 7a

(select distinct grp, hub, sub_id

from feeders_new where grp in (select groupname from autoloadflow_lineresults) ) t7b, -- Table 7b

(select id, alpha from substations ) t7c -- Table 7c

where t7a.grp = t7b.grp and

t7b.sub_id = t7c.id ) t7, -- Table 7

( -- Table 8

-- DINIS Loadflow Summary Results

select groupname, errornotes, daterun

from autoloadflow_summaryresults ) t8, -- Table 8

( -- Table 9

-- DINIS Loadflow Voltage Regulation

select groupname, ( max(voltagemagnitude)-min(voltagemagnitude) ) *100 dinis_grp_volt_reg

from autoloadflow_noderesults

group by groupname ) t9, -- Table 9

( -- Table 10

-- DINIS Group Power Loss

select groupname, round (sum((realpowerloss*100*power(10,6))/1000),2) dinis_real_power_loss

from autoloadflow_lineresults

group by groupname ) t10, -- Table 10

( -- Table 11

-- Autoloadflow_Limits_Projects

select project_no, group_name,

(case when (rating_limit = 'YES' and voltage_limit is null) then 'RATING'

when (rating_limit is null and voltage_limit = 'YES') then 'VOLTAGE'

when (rating_limit = 'YES' and voltage_limit = 'YES') then 'RATING and VOLTAGE'

when (rating_limit is null and voltage_limit is null) then null

else 'XXXXXXXX' end) project_type,

from_, to_, dinis_line_gor, comments, pow, last_update_time, date_completed

from autoloadflow_limits_projects

order by group_name ) t11 -- Table 11

where t6.groupname = t7.grp and -- Join Tables 6, 7, 8, 9, 10 & 11

t6.groupname = t8.groupname and

t6.groupname = t9.groupname and

t6.groupname = t10.groupname and

t6.line_gor = t11.dinis_line_gor (+)

)

)

where groupname like '%' and

hub like '%'

order by groupname, worst_utilisation desc

- 877 Views
- Tags: none (add)