Tags:

Oracle CONNECT BY Anzeige der maximalen Verkettung

Von Tobias Arnhold 12.26.2014
Der Umgang mit CONNECT BY ist für mich immer wieder ein Highlight. Ob Positiv oder Negativ lasse ich mal außen vor. :)
Unbeachtet meiner Meinung ist es die sinnvollste Lösung um Baum-Verkettungen zu generieren.

Eine Anforderung die ich zuletzt gleich zweimal lösen musste war die Darstellung der finalen Ketten.

Beispiel Quell-Daten:
1:
1:2
1:2:3
1:2:4
1:6
1:6:1

Beispiel Ziel-Daten:
1:2:3
1:2:4
1:6:1

Das Ganze war recht einfach mit Hilfe einer analytischen Funktion zu lösen:
-- Tabele Definition
  CREATE TABLE "T_ROUTE_EXAMPLE" 
   ( 
    "ID" NUMBER, 
 "P_ID" NUMBER, 
 "NAME" VARCHAR2(50), 
 "TYPE" VARCHAR2(20),
 PRIMARY KEY ("ID")
   ) ;

-- Data
REM INSERTING into T_ROUTE_EXAMPLE
SET DEFINE OFF;
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('1',null,'Haus S','BUILDING');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('3','1','Switch_S_1','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('4','3','1/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('5','3','2/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('6','3','3/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('7','1','Switch_S_2','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('8','7','1/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('9','7','2/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('10','4','DHCP Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('11','5','orclapex DB Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('12','6','ORDS Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('13','8','WEB Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('14','9','AD Server','SERVER');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('15',null,'Haus G','BUILDING');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('16','15','Switch_G_1','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('17','16','1/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('18','16','2/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('19','16','3/3','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('20','17','Win Client 1','CLIENT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('21','18','Win Client 2','CLIENT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('22','19','Mac Client 1','CLIENT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('23',null,'Haus D','BUILDING');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('24','23','Switch_D_1','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('25','24','1/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('26','24','2/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('27','23','Switch_D_2','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('28','27','1/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('29','27','2/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('30','23','Switch_D_3','SWITCH');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('31','30','1/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('32','30','2/2','PORT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('33','28','Mac Client 2','CLIENT');
Insert into T_ROUTE_EXAMPLE (ID,P_ID,NAME,TYPE) values ('34','29','Mac Client 3','CLIENT');
Hier das Select um das entsprechende Ergebnis zu generieren:
SELECT         
      PATH_LENGTH, 
      ID,
      P_ID,
      NAME,
      TYPE,
      ID_LIST,
      NAME_LIST,
      TYPE_LIST
FROM 
  (
  SELECT
        case 
           when instr(':'||LEAD(ID_LIST) OVER (ORDER BY ID_LIST)||':',':'||ID_LIST||':') > 0 
            then null 
            else 'OK' 
        end as FINAL_PATH,
        PATH_LENGTH, 
        ID,
        P_ID,
        NAME,
        TYPE,
        ID_LIST,
        NAME_LIST,
        TYPE_LIST
  FROM 
      (
      SELECT 
            LEVEL as PATH_LENGTH, 
            ID,
            P_ID,
            NAME,
            TYPE,
            SUBSTR(SYS_CONNECT_BY_PATH(ID, ':'),2) as ID_LIST,
            SUBSTR(SYS_CONNECT_BY_PATH(NAME, ':'),2) as NAME_LIST,
            SUBSTR(SYS_CONNECT_BY_PATH(TYPE, ':'),2) as TYPE_LIST
      FROM T_ROUTE_EXAMPLE M1
      start with M1.P_ID is null
      CONNECT BY prior M1.ID = M1.P_ID
      )
  )
WHERE FINAL_PATH = 'OK'

Post Tags:

No Comment to " Oracle CONNECT BY Anzeige der maximalen Verkettung "