My journey in pl/sql from the course The Complete PL/SQL Bootcamp : "Beginner to Advanced PL/SQL" from udemy
CREATE OR REPLACE FORCE EDITIONABLE VIEW "DWSTAGE"."ADMS_DEF_CD_CTRL_ZONE_VW" ("CONTROL_ZONE_ID", "CONTROL_ZONE_NAME", "HIERARCHY_TYPE", "HIERARCHY_LEVEL", "PARENT_NCG_LVL1_ID", "PARENT_NCG_LVL1_NAME", "PARENT_NCG_LVL2_ID", "PARENT_NCG_LVL2_NAME", "PARENT_NCG_LVL3_ID", "PARENT_NCG_LVL3_NAME", "PARENT_NCG_LVL4_ID", "PARENT_NCG_LVL4_NAME", "PARENT_NCG_LVL5_ID", "PARENT_NCG_LVL5_NAME", "PARENT_NCG_LVL6_ID", "PARENT_NCG_LVL6_NAME", "PARENT_NCG_LVL7_ID", "PARENT_NCG_LVL7_NAME", "PARENT_NCG_LVL8_ID", "PARENT_NCG_LVL8_NAME", "JRN_UPDATE_DTTM", "RELAY", "OPERATED_FEEDER") AS WITH level1 AS (SELECT CAST ('1' AS NVARCHAR2 (2000)) PARENT_NCG_LVL1, ORG_UNIT_ID PARENT_NCG_LVL1_ID, ORG_UNIT_NAME PARENT_NCG_LVL1_NAME, CAST ('' AS NVARCHAR2 (2000)) PARENT_NCG_LVL2, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL2_ID, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL2_NAME, CAST ('' AS NVARCHAR2 (2000)) PARENT_NCG_LVL3, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL3_ID, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL3_NAME, CAST ('' AS NVARCHAR2 (2000)) PARENT_NCG_LVL4, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL4_ID, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL4_NAME, CAST ('' AS NVARCHAR2 (2000)) PARENT_NCG_LVL5, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL5_ID, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL5_NAME, CAST ('' AS NVARCHAR2 (2000)) PARENT_NCG_LVL6, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL6_ID, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL6_NAME, CAST ('' AS NVARCHAR2 (2000)) PARENT_NCG_LVL7, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL7_ID, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL7_NAME, CAST ('' AS NVARCHAR2 (2000)) PARENT_NCG_LVL8, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL8_ID, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL8_NAME, TO_DATE ('01/01/1900 00:00:00', 'MM/DD/YYYY HH24:MI:SS') JRN_UPDATE_DTTM, 'RELAY1' RELAY FROM DEFREP.ORG_UNIT WHERE ORG_UNIT_ID = 'DEF'), level_2t06_flattened AS (SELECT PARENT_NCG_LVL1, PARENT_NCG_LVL1_ID, (SELECT ORG_UNIT_NAME FROM DEFREP.ORG_UNIT WHERE SHORT_NAME = PARENT_NCG_LVL1_ID) PARENT_NCG_LVL1_NAME, PARENT_NCG_LVL2, PARENT_NCG_LVL2_ID, (SELECT ORG_UNIT_NAME FROM DEFREP.ORG_UNIT WHERE SHORT_NAME = PARENT_NCG_LVL2_ID) PARENT_NCG_LVL2_NAME, PARENT_NCG_LVL3, PARENT_NCG_LVL3_ID, (SELECT ORG_UNIT_NAME FROM DEFREP.ORG_UNIT WHERE SHORT_NAME = PARENT_NCG_LVL3_ID) PARENT_NCG_LVL3_NAME, PARENT_NCG_LVL4, PARENT_NCG_LVL4_ID, (SELECT ORG_UNIT_NAME FROM DEFREP.ORG_UNIT WHERE SHORT_NAME = PARENT_NCG_LVL4_ID) PARENT_NCG_LVL4_NAME, PARENT_NCG_LVL5, PARENT_NCG_LVL5_ID, (SELECT ORG_UNIT_NAME FROM DEFREP.ORG_UNIT WHERE SHORT_NAME = PARENT_NCG_LVL5_ID) PARENT_NCG_LVL5_NAME, PARENT_NCG_LVL6, PARENT_NCG_LVL6_ID, (SELECT ORG_UNIT_NAME FROM DEFREP.ORG_UNIT WHERE SHORT_NAME = PARENT_NCG_LVL6_ID) PARENT_NCG_LVL6_NAME, JRN_UPDATE_DTTM, 'RELAY2TO6' RELAY FROM (SELECT REGEXP_SUBSTR (ORG_LEVEL, '[^/]+', 1, 1) PARENT_NCG_LVL1, REGEXP_SUBSTR (ORG_ID, '[^/]+', 1, 1) PARENT_NCG_LVL1_ID, REGEXP_SUBSTR (ORG_LEVEL, '[^/]+', 1, 2) PARENT_NCG_LVL2, REGEXP_SUBSTR (ORG_ID, '[^/]+', 1, 2) PARENT_NCG_LVL2_ID, REGEXP_SUBSTR (ORG_LEVEL, '[^/]+', 1, 3) PARENT_NCG_LVL3, REGEXP_SUBSTR (ORG_ID, '[^/]+', 1, 3) PARENT_NCG_LVL3_ID, REGEXP_SUBSTR (ORG_LEVEL, '[^/]+', 1, 4) PARENT_NCG_LVL4, REGEXP_SUBSTR (ORG_ID, '[^/]+', 1, 4) PARENT_NCG_LVL4_ID, REGEXP_SUBSTR (ORG_LEVEL, '[^/]+', 1, 5) PARENT_NCG_LVL5, REGEXP_SUBSTR (ORG_ID, '[^/]+', 1, 5) PARENT_NCG_LVL5_ID, REGEXP_SUBSTR (ORG_LEVEL, '[^/]+', 1, 6) PARENT_NCG_LVL6, REGEXP_SUBSTR (ORG_ID, '[^/]+', 1, 6) PARENT_NCG_LVL6_ID, JRN_UPDATE_DTTM FROM ( SELECT ORG_UNIT_HIERARCHY_ID, ORG_HIERARCHY_TYPE_ID, PARENT_ORG_UNIT_ID, PARENT_ORG_UNIT_NAME, PARENT_ORG_UNIT_SHORT_NAME, PARENT_ORG_UNIT_TYPE_ID, CHILD_ORG_UNIT_ID, CHILD_ORG_UNIT_NAME, CHILD_ORG_UNIT_SHORT_NAME, CHILD_ORG_UNIT_TYPE_ID, JRN_UPDATE_DTTM, '1' || SYS_CONNECT_BY_PATH ( CHILD_ORG_UNIT_TYPE_ID, '/') ORG_LEVEL, 'DEF' || SYS_CONNECT_BY_PATH ( CHILD_ORG_UNIT_ID, '/') ORG_ID FROM (SELECT OUH.ORG_UNIT_HIERARCHY_ID, OUH.ORG_HIERARCHY_TYPE_ID, OUH.PARENT_ORG_UNIT_ID, OU1.ORG_UNIT_NAME PARENT_ORG_UNIT_NAME, OU1.SHORT_NAME PARENT_ORG_UNIT_SHORT_NAME, OU1.ORG_UNIT_TYPE_ID PARENT_ORG_UNIT_TYPE_ID, OUH.CHILD_ORG_UNIT_ID, OU2.ORG_UNIT_NAME CHILD_ORG_UNIT_NAME, OU2.SHORT_NAME CHILD_ORG_UNIT_SHORT_NAME, OU2.ORG_UNIT_TYPE_ID CHILD_ORG_UNIT_TYPE_ID, OUH.JRN_UPDATE_DTTM FROM (SELECT * FROM DEFREP.ORG_UNIT_HIERARCHY WHERE JRN_FLAG <> 'D') OUH LEFT OUTER JOIN DEFREP.ORG_UNIT OU1 ON OUH.PARENT_ORG_UNIT_ID = OU1.ORG_UNIT_ID LEFT OUTER JOIN DEFREP.ORG_UNIT OU2 ON OUH.CHILD_ORG_UNIT_ID = OU2.ORG_UNIT_ID) START WITH PARENT_ORG_UNIT_ID = 'DEF' CONNECT BY PRIOR CHILD_ORG_UNIT_ID = PARENT_ORG_UNIT_ID))), level7 AS (SELECT a., CAST ( CASE WHEN STATION.STATION_ID IS NOT NULL THEN '7' ELSE '' END AS NVARCHAR2 (2000)) PARENT_NCG_LVL7, CAST (STATION.STATION_ID AS NVARCHAR2 (50)) PARENT_NCG_LVL7_ID, CAST (STATION.STATION_NAME AS NVARCHAR2 (50)) PARENT_NCG_LVL7_NAME, STATION.JRN_UPDATE_DTTM, 'RELAY7' RELAY FROM (SELECT PARENT_NCG_LVL1, PARENT_NCG_LVL1_ID, PARENT_NCG_LVL1_NAME, PARENT_NCG_LVL2, PARENT_NCG_LVL2_ID, PARENT_NCG_LVL2_NAME, PARENT_NCG_LVL3, PARENT_NCG_LVL3_ID, PARENT_NCG_LVL3_NAME, PARENT_NCG_LVL4, PARENT_NCG_LVL4_ID, PARENT_NCG_LVL4_NAME, PARENT_NCG_LVL5, PARENT_NCG_LVL5_ID, PARENT_NCG_LVL5_NAME, PARENT_NCG_LVL6, PARENT_NCG_LVL6_ID, PARENT_NCG_LVL6_NAME FROM level_2t06_flattened WHERE PARENT_NCG_LVL6_ID IS NOT NULL) a JOIN (SELECT DISTINCT SERVICE_CENTER_ID, STATION_ID, STATION_NAME, JRN_UPDATE_DTTM FROM ( /SELECT fdr.c_id, fdr.c_name, sub.c_servcntr SERVICE_CENTER_ID, sub.SUBNAME STATION_NAME, sub.c_id STATION_ID, sub.jrn_update_dttm FROM DNOM_DEFREP.dt_swt fdr INNER JOIN DWSTAGE.ADMS_EGSDS_ED_DEVICE_STG egs_stg ON fdr.c_name = egs_stg.NETWORK_ID AND egs_stg.DEVICE_TYPE_CD = 'BREAKER' AND fdr.subname = egs_stg.SUBSTATION_NAME INNER JOIN DNOM_DEFREP.dt_sub sub ON fdr.SUBNAME = sub.SUBNAME WHERE fdr.jrn_flag <> 'D' AND sub.jrn_flag <> 'D' AND fdr.c_devtype = 4)/ -- Commented out this section of code and replaced it with the SQL below to increase the match on the feeders. AF 10/7/2022 SELECT DISTINCT sub.c_servcntr SERVICE_CENTER_ID, sub.SUBNAME STATION_NAME, sub.c_id STATION_ID, sub.jrn_update_dttm FROM (SELECT swt.c_id, ---NVL (feeder.c_id, swt.c_name) CASE WHEN LENGTH (feeder.c_id) > 10 THEN swt.c_name ELSE NVL (feeder.c_id, swt.c_name) END c_name, swt.subname, swt.c_devtype, swt.JRN_FLAG, swt.JRN_UPDATE_DTTM FROM DNOM_DEFREP.dt_swt swt LEFT JOIN DNOM_DEFREP.dt_fdr feeder ON swt.c_id = SUBSTR ( feeder.c_nominalbreaker, 8) AND swt.subname = feeder.subname AND feeder.jrn_flag <> 'D' WHERE swt.jrn_flag <> 'D' AND swt.c_devtype = 4) fdr INNER JOIN DWSTAGE.ADMS_EGSDS_ED_DEVICE_STG egs_stg ON fdr.c_name = egs_stg.NETWORK_ID AND egs_stg.DEVICE_TYPE_CD = 'BREAKER' AND fdr.subname = egs_stg.SUBSTATION_NAME INNER JOIN DNOM_DEFREP.dt_sub sub ON fdr.SUBNAME = sub.SUBNAME WHERE fdr.jrn_flag <> 'D' AND sub.jrn_flag <> 'D' AND fdr.c_devtype = 4)) STATION ON a.PARENT_NCG_LVL6_ID = STATION.SERVICE_CENTER_ID --where a.PARENT_NCG_LVL6 is not null ), level8 AS (SELECT a., CAST ( CASE WHEN FEEDER.FEEDER_ID IS NOT NULL THEN '8' ELSE '' END AS NVARCHAR2 (2000)) PARENT_NCG_LVL8, CAST (FEEDER.FEEDER_ID AS NVARCHAR2 (50)) PARENT_NCG_LVL8_ID, CAST (FEEDER.FEEDER_NAME AS NVARCHAR2 (50)) PARENT_NCG_LVL8_NAME, FEEDER.JRN_UPDATE_DTTM,OPERATED_FEEDER, 'RELAY8' RELAY FROM (SELECT PARENT_NCG_LVL1, PARENT_NCG_LVL1_ID, PARENT_NCG_LVL1_NAME, PARENT_NCG_LVL2, PARENT_NCG_LVL2_ID, PARENT_NCG_LVL2_NAME, PARENT_NCG_LVL3, PARENT_NCG_LVL3_ID, PARENT_NCG_LVL3_NAME, PARENT_NCG_LVL4, PARENT_NCG_LVL4_ID, PARENT_NCG_LVL4_NAME, PARENT_NCG_LVL5, PARENT_NCG_LVL5_ID, PARENT_NCG_LVL5_NAME, PARENT_NCG_LVL6, PARENT_NCG_LVL6_ID, PARENT_NCG_LVL6_NAME, PARENT_NCG_LVL7, PARENT_NCG_LVL7_ID, PARENT_NCG_LVL7_NAME FROM level7 WHERE PARENT_NCG_LVL7_ID IS NOT NULL) a JOIN (SELECT FEEDER_ID, STATION_ID, FEEDER_NAME, NULL OPERATED_FEEDER, JRN_UPDATE_DTTM FROM /(SELECT egs_stg.id feeder_id, fdr.c_name feeder_name, sub.c_servcntr c_servcntr, sub.SUBNAME station_name, sub.c_id station_id, fdr.jrn_update_dttm FROM DNOM_DEFREP.dt_swt fdr INNER JOIN DWSTAGE.ADMS_EGSDS_ED_DEVICE_STG egs_stg ON fdr.c_name = egs_stg.NETWORK_ID AND egs_stg.DEVICE_TYPE_CD = 'BREAKER' AND fdr.subname = egs_stg.SUBSTATION_NAME INNER JOIN DNOM_DEFREP.dt_sub sub ON fdr.SUBNAME = sub.SUBNAME WHERE fdr.jrn_flag <> 'D' AND sub.jrn_flag <> 'D' AND fdr.c_devtype = 4)/ -- Commented out this section of code and replaced it with the SQL below to increase the match on the feeders. AF 9/27/2022 (SELECT DISTINCT egs_stg.id feeder_id, fdr.c_name feeder_name, sub.c_servcntr c_servcntr, sub.SUBNAME station_name, sub.c_id station_id, fdr.OPERATED_FEEDER, fdr.jrn_update_dttm FROM (SELECT swt.c_id, --NVL (feeder.c_id, swt.c_name) CASE WHEN LENGTH (feeder.c_id) > 10 THEN swt.c_name ELSE NVL (feeder.c_id, swt.c_name) END c_name, swt.subname, feeder.c_name OPERATED_FEEDER, swt.c_devtype, swt.JRN_FLAG, swt.JRN_UPDATE_DTTM FROM DNOM_DEFREP.dt_swt swt LEFT JOIN DNOM_DEFREP.dt_fdr feeder ON swt.c_id = SUBSTR ( feeder.c_nominalbreaker, 8) AND swt.subname = feeder.subname AND feeder.jrn_flag <> 'D' WHERE swt.jrn_flag <> 'D' AND swt.c_devtype = 4) fdr INNER JOIN DWSTAGE.ADMS_EGSDS_ED_DEVICE_STG egs_stg ON fdr.c_name = egs_stg.NETWORK_ID AND egs_stg.DEVICE_TYPE_CD = 'BREAKER' AND fdr.subname = egs_stg.SUBSTATION_NAME INNER JOIN DNOM_DEFREP.dt_sub sub ON fdr.SUBNAME = sub.SUBNAME WHERE fdr.jrn_flag <> 'D' AND sub.jrn_flag <> 'D' AND fdr.c_devtype = 4)) FEEDER ON a.PARENT_NCG_LVL7_ID = FEEDER.STATION_ID), all_levels AS (SELECT PARENT_NCG_LVL1, PARENT_NCG_LVL1_ID, PARENT_NCG_LVL1_NAME, PARENT_NCG_LVL2, PARENT_NCG_LVL2_ID, PARENT_NCG_LVL2_NAME, PARENT_NCG_LVL3, PARENT_NCG_LVL3_ID, PARENT_NCG_LVL3_NAME, PARENT_NCG_LVL4, PARENT_NCG_LVL4_ID, PARENT_NCG_LVL4_NAME, PARENT_NCG_LVL5, PARENT_NCG_LVL5_ID, PARENT_NCG_LVL5_NAME, PARENT_NCG_LVL6, PARENT_NCG_LVL6_ID, PARENT_NCG_LVL6_NAME, PARENT_NCG_LVL7, PARENT_NCG_LVL7_ID, PARENT_NCG_LVL7_NAME, PARENT_NCG_LVL8, PARENT_NCG_LVL8_ID, PARENT_NCG_LVL8_NAME, JRN_UPDATE_DTTM, RELAY , cast('' as NVARCHAR2(2)) OPERATED_FEEDER FROM level1 UNION ALL SELECT PARENT_NCG_LVL1, PARENT_NCG_LVL1_ID, PARENT_NCG_LVL1_NAME, PARENT_NCG_LVL2, PARENT_NCG_LVL2_ID, PARENT_NCG_LVL2_NAME, PARENT_NCG_LVL3, PARENT_NCG_LVL3_ID, PARENT_NCG_LVL3_NAME, PARENT_NCG_LVL4, PARENT_NCG_LVL4_ID, PARENT_NCG_LVL4_NAME, PARENT_NCG_LVL5, PARENT_NCG_LVL5_ID, PARENT_NCG_LVL5_NAME, PARENT_NCG_LVL6, PARENT_NCG_LVL6_ID, PARENT_NCG_LVL6_NAME, CAST ('' AS NVARCHAR2 (2000)) PARENT_NCG_LVL7, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL7_ID, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL7_NAME, CAST ('' AS NVARCHAR2 (2000)) PARENT_NCG_LVL8, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL8_ID, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL8_NAME, JRN_UPDATE_DTTM, RELAY , cast('' as NVARCHAR2(2)) OPERATED_FEEDER FROM level_2t06_flattened UNION ALL SELECT DISTINCT PARENT_NCG_LVL1, PARENT_NCG_LVL1_ID, PARENT_NCG_LVL1_NAME, PARENT_NCG_LVL2, PARENT_NCG_LVL2_ID, PARENT_NCG_LVL2_NAME, PARENT_NCG_LVL3, PARENT_NCG_LVL3_ID, PARENT_NCG_LVL3_NAME, PARENT_NCG_LVL4, PARENT_NCG_LVL4_ID, PARENT_NCG_LVL4_NAME, PARENT_NCG_LVL5, PARENT_NCG_LVL5_ID, PARENT_NCG_LVL5_NAME, PARENT_NCG_LVL6, PARENT_NCG_LVL6_ID, PARENT_NCG_LVL6_NAME, PARENT_NCG_LVL7, PARENT_NCG_LVL7_ID, PARENT_NCG_LVL7_NAME, CAST ('' AS NVARCHAR2 (2000)) PARENT_NCG_LVL8, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL8_ID, CAST ('' AS NVARCHAR2 (50)) PARENT_NCG_LVL8_NAME, JRN_UPDATE_DTTM, RELAY , cast('' as NVARCHAR2(2)) OPERATED_FEEDER FROM level7 UNION ALL SELECT PARENT_NCG_LVL1, PARENT_NCG_LVL1_ID, PARENT_NCG_LVL1_NAME, PARENT_NCG_LVL2, PARENT_NCG_LVL2_ID, PARENT_NCG_LVL2_NAME, PARENT_NCG_LVL3, PARENT_NCG_LVL3_ID, PARENT_NCG_LVL3_NAME, PARENT_NCG_LVL4, PARENT_NCG_LVL4_ID, PARENT_NCG_LVL4_NAME, PARENT_NCG_LVL5, PARENT_NCG_LVL5_ID, PARENT_NCG_LVL5_NAME, PARENT_NCG_LVL6, PARENT_NCG_LVL6_ID, PARENT_NCG_LVL6_NAME, PARENT_NCG_LVL7, PARENT_NCG_LVL7_ID, PARENT_NCG_LVL7_NAME, PARENT_NCG_LVL8, PARENT_NCG_LVL8_ID, PARENT_NCG_LVL8_NAME, JRN_UPDATE_DTTM, RELAY, cast(OPERATED_FEEDER as NVARCHAR2(50)) OPERATED_FEEDER FROM level8) SELECT DISTINCT CASE WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '1' THEN PARENT_NCG_LVL1_ID WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '2' THEN PARENT_NCG_LVL2_ID WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '3' THEN PARENT_NCG_LVL3_ID WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '4' THEN PARENT_NCG_LVL4_ID WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '5' THEN PARENT_NCG_LVL5_ID WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '6' THEN PARENT_NCG_LVL6_ID WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '7' THEN PARENT_NCG_LVL7_ID WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '8' THEN PARENT_NCG_LVL8_ID END CONTROL_ZONE_ID, CASE WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '1' THEN PARENT_NCG_LVL1_NAME WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '2' THEN PARENT_NCG_LVL2_NAME WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '3' THEN PARENT_NCG_LVL3_NAME WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '4' THEN PARENT_NCG_LVL4_NAME WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '5' THEN PARENT_NCG_LVL5_NAME WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '6' THEN PARENT_NCG_LVL6_NAME WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '7' THEN PARENT_NCG_LVL7_NAME WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '8' THEN PARENT_NCG_LVL8_NAME END CONTROL_ZONE_NAME, CASE WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '1' THEN 'REG' WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '2' THEN 'ZONE' WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '3' THEN 'AREA' WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '4' THEN 'OPS' WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '5' THEN 'SUBC' WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '6' THEN 'TRAREA' WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '7' THEN 'SUBS' WHEN COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) = '8' THEN 'CIR' END HIERARCHY_TYPE, COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1) HIERARCHY_LEVEL, PARENT_NCG_LVL1_ID, PARENT_NCG_LVL1_NAME, PARENT_NCG_LVL2_ID, PARENT_NCG_LVL2_NAME, PARENT_NCG_LVL3_ID, PARENT_NCG_LVL3_NAME, PARENT_NCG_LVL4_ID, PARENT_NCG_LVL4_NAME, PARENT_NCG_LVL5_ID, PARENT_NCG_LVL5_NAME, PARENT_NCG_LVL6_ID, PARENT_NCG_LVL6_NAME, PARENT_NCG_LVL7_ID, PARENT_NCG_LVL7_NAME, PARENT_NCG_LVL8_ID, PARENT_NCG_LVL8_NAME, JRN_UPDATE_DTTM, RELAY , OPERATED_FEEDER FROM all_levels ORDER BY COALESCE (PARENT_NCG_LVL8, PARENT_NCG_LVL7, PARENT_NCG_LVL6, PARENT_NCG_LVL5, PARENT_NCG_LVL4, PARENT_NCG_LVL3, PARENT_NCG_LVL2, PARENT_NCG_LVL1);