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:
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'
