Home » SQL & PL/SQL » SQL & PL/SQL » Oracle - path passing by multiple nodes (oracle 12c)
Oracle - path passing by multiple nodes [message #677742] |
Thu, 10 October 2019 06:18 |
|
ghostman
Messages: 16 Registered: October 2019
|
Junior Member |
|
|
I have the following oracle 12c tables structure:
table1:
path_id node_1 node1_port node_2 node2_port
--------------------------------------------------------------------------------
1 nodeA nodeA_port1 nodeB nodeB_port1
1 nodeB nodeB_port1 nodeA nodeA_port1
1 nodeB nodeB_port2 nodeC nodeC_port1
1 nodeC nodeC_port1 nodeB nodeB_port2
1 nodeC nodeC_port2 nodeD nodeD_port1
1 nodeD nodeD_port1 nodeC nodeC_port2
2 nodeC nodeC_port1 nodeF nodeF_port1
2 nodeF nodeF_port1 nodeC nodeC_port1
table2:
path_id start_node end_node
-----------------------------------------
1 nodeA nodeD
2 nodeC nodeF
where path id has multiple nodes and node_1_port and node_2_port represent the hops of this path.
so for example path_id = 1 start from node1 and end in node 4 (as per table2), passing by node2 and node3 (as per table1), while path_id = 2 start from node5 and end in node6.
In addition, node_1 and node_2 can be random names. the order of the hops will be got from the connection of node_1 and node_2 : nodeA is connected to nodeB , then nodeB connected to nodeC , then nodeC connected to nodeD , then the order of the path is : nodeA-->nodeB-->nodeC-->nodeD , knowing that the port connecting nodeB to nodeA is different than the port connecting nodeB to nodeC , and both ports must be included in the final table. It's a bit complicated , I'm not able to get it right.
this is the needed output:
path_id node node_port order
---------------------------------------------------
1 nodeA nodeA_port1 1
1 nodeB nodeB_port1 2
1 nodeB nodeB_port2 3
1 nodeC nodeC_port1 4
1 nodeC nodeC_port2 5
1 nodeD nodeD_port1 6
2 nodeC nodeC_port1 1
2 nodeF nodeF_port1 2
can you give me some hints how to proceed as I'm still new to oracle.
|
|
|
|
Re: Oracle - path passing by multiple nodes [message #677749 is a reply to message #677744] |
Fri, 11 October 2019 01:23 |
|
ghostman
Messages: 16 Registered: October 2019
|
Junior Member |
|
|
here are the input tables:
create table table1 as (
select 1 path_id ,'nodeZ' node_1,'nodeZ_port1' node1_port,'nodeY' node_2,'nodeY_port1' node2_port from dual
UNION select 1 path_id ,'nodeY' node_1,'nodeY_port1' node1_port,'nodeZ' node_2,'nodeZ_port1' node2_port from dual
UNION select 1 path_id ,'nodeY' node_1,'nodeY_port2' node1_port,'nodeC' node_2,'nodeC_port1' node2_port from dual
UNION select 1 path_id ,'nodeC' node_1,'nodeC_port1' node1_port,'nodeY' node_2,'nodeY_port2' node2_port from dual
UNION select 1 path_id ,'nodeC' node_1,'nodeC_port2' node1_port,'nodeD' node_2,'nodeD_port1' node2_port from dual
UNION select 1 path_id ,'nodeD' node_1,'nodeD_port1' node1_port,'nodeC' node_2,'nodeC_port2' node2_port from dual
UNION select 2 path_id ,'nodeC' node_1,'nodeC_port1' node1_port,'nodeF' node_2,'nodeF_port1' node2_port from dual
UNION select 2 path_id ,'nodeF' node_1,'nodeF_port1' node1_port,'nodeC' node_2,'nodeC_port1' node2_port from dual
)
create table table2 as (
select 1 path_id ,'nodeZ' start_node ,'nodeZ_port1' start_node_port ,'nodeD' end_node , 'nodeD_port1' end_node_port from dual
UNION select 2 path_id ,'nodeC' start_node ,'nodeC_port1' start_node_port ,'nodeF' end_node , 'nodeF_port1' end_node_port from dual
)
And here is the output needed :
path_id node node_port order
---------------------------------------------------
1 nodeA nodeA_port1 1
1 nodeB nodeB_port1 2
1 nodeB nodeB_port2 3
1 nodeC nodeC_port1 4
1 nodeC nodeC_port2 5
1 nodeD nodeD_port1 6
2 nodeC nodeC_port1 1
2 nodeF nodeF_port1 2
Here is my try which is working, but it's very bad as it uses multiple loops. What I need is to get the result using simple query
declare
id integer :=1;
start_node varchar2(50);
end_node varchar2(50);
start_node_port varchar2(50);
end_node_port varchar2(50);
cnt integer:=1;
cnt_or integer:=1;
cnt1 integer:=0;
TYPE nodes_arr IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
nodes nodes_arr;
TYPE nodes_ports_arr IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;
ports nodes_ports_arr;
id_cnt integer;
BEGIN
select max(path_id) into id_cnt from table2;
while id <= id_cnt
LOOP
select start_node,end_node,start_node_port,end_node_port into nodes(1),end_node,ports(1),end_node_port
from table2 where path_id = id;
start_node:=nodes(1);
start_node_port:=ports(1);
insert into table_final
select id,nodes(1),ports(1),cnt from dual;
commit;
while start_node!=end_node
LOOP
cnt:=cnt+1;
cnt_or:=cnt_or+1;
if cnt =2
THEN
select node_2,node2_port into nodes(cnt),ports(cnt)
from table1 where path_id=id and node_1=nodes(cnt-1) and node_2!=node_1;
ELSE
select node_2,node2_port into nodes(cnt),ports(cnt)
from table1 where path_id=id and node_1=nodes(cnt-1) and node_2!=node_1 and node_2!=nodes(cnt-2) ;
END IF;
insert into table_final
select id,nodes(cnt),ports(cnt),cnt_or from dual;
select NVL(count(*),0) into cnt1 from table1 where path_id=id and node_1=nodes(cnt) and node1_port !=ports(cnt);
if cnt1=1
THEN
cnt_or:=cnt_or+1;
insert into table_final
select id,node_1,node1_port,cnt_or
from table1 where path_id=id and node_1=nodes(cnt) and node1_port !=ports(cnt);
END IF;
start_node:=nodes(cnt);
start_node_port:=ports(cnt);
commit;
END LOOP;
cnt:=1;
cnt_or:=1;
id:=id+1;
END LOOP;
END;
|
|
|
|
Re: Oracle - path passing by multiple nodes [message #677756 is a reply to message #677754] |
Fri, 11 October 2019 04:18 |
|
ghostman
Messages: 16 Registered: October 2019
|
Junior Member |
|
|
Sorry for that , here is the right input :
create table table1 as (
select 1 path_id ,'nodeA' node_1,'nodeA_port1' node1_port,'nodeB' node_2,'nodeB_port1' node2_port from dual
UNION select 1 path_id ,'nodeB' node_1,'nodeB_port1' node1_port,'nodeA' node_2,'nodeA_port1' node2_port from dual
UNION select 1 path_id ,'nodeB' node_1,'nodeB_port2' node1_port,'nodeC' node_2,'nodeC_port1' node2_port from dual
UNION select 1 path_id ,'nodeC' node_1,'nodeC_port1' node1_port,'nodeB' node_2,'nodeB_port2' node2_port from dual
UNION select 1 path_id ,'nodeC' node_1,'nodeC_port2' node1_port,'nodeD' node_2,'nodeD_port1' node2_port from dual
UNION select 1 path_id ,'nodeD' node_1,'nodeD_port1' node1_port,'nodeC' node_2,'nodeC_port2' node2_port from dual
UNION select 2 path_id ,'nodeC' node_1,'nodeC_port1' node1_port,'nodeF' node_2,'nodeF_port1' node2_port from dual
UNION select 2 path_id ,'nodeF' node_1,'nodeF_port1' node1_port,'nodeC' node_2,'nodeC_port1' node2_port from dual
)
create table table2 as (
select 1 path_id ,'nodeA' start_node ,'nodeA_port1' start_node_port ,'nodeD' end_node , 'nodeD_port1' end_node_port from dual
UNION select 2 path_id ,'nodeC' start_node ,'nodeC_port1' start_node_port ,'nodeF' end_node , 'nodeF_port1' end_node_port from dual
)
by node_1_port I meant node1_port , and by "start from node1 and end in node 4 (as per table2)" I meant "start from nodeA and end in nodeD (as per table2)"
|
|
|
Re: Oracle - path passing by multiple nodes [message #677765 is a reply to message #677756] |
Fri, 11 October 2019 06:25 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
Have a look at Hierarchical Queries.
As a start:
WITH tab1(path_id, node_1, node1_port, node_2, node2_port) AS
(SELECT 1 ,'nodeA' ,'nodeA_port1' ,'nodeB' ,'nodeB_port1' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port1' ,'nodeA' ,'nodeA_port1' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port2' ,'nodeC' ,'nodeC_port1' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port1' ,'nodeB' ,'nodeB_port2' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port2' ,'nodeD' ,'nodeD_port1' FROM dual UNION ALL
SELECT 1 ,'nodeD' ,'nodeD_port1' ,'nodeC' ,'nodeC_port2' FROM dual UNION ALL
SELECT 2 ,'nodeC' ,'nodeC_port1' ,'nodeF' ,'nodeF_port1' FROM dual UNION ALL
SELECT 2 ,'nodeF' ,'nodeF_port1' ,'nodeC' ,'nodeC_port1' FROM dual)
SELECT path_id
, node_1 node, node1_port node_port
, rownum rn
FROM tab1
START WITH node_1='nodeA' AND path_id=1
CONNECT BY
node_1 = prior node_2
AND prior path_id = path_id
AND prior node_1 < node_1
ORDER BY level, node1_port;
PATH_ID NODE NODE_PORT RN
-------------------------------------
1 nodeA nodeA_port1 1
1 nodeB nodeB_port1 2
1 nodeB nodeB_port2 3
1 nodeC nodeC_port1 4
1 nodeC nodeC_port2 5
1 nodeD nodeD_port1 6
[Updated on: Fri, 11 October 2019 06:25] Report message to a moderator
|
|
|
|
|
|
Re: Oracle - path passing by multiple nodes [message #677779 is a reply to message #677778] |
Fri, 11 October 2019 08:52 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
So put your connection conditions in the query and omit cycles.
Alternatively you could use Recursive subquery factoring (LiveSQL) they are more powerful.
Here (simplified) the CONNECT BY example translated into Recursive subquery factoring/CTE:
WITH tab1(path_id, node_1, node1_port, node_2, node2_port) AS
(SELECT 1 ,'nodeA' ,'nodeA_port1' ,'nodeB' ,'nodeB_port1' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port1' ,'nodeA' ,'nodeA_port1' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port2' ,'nodeC' ,'nodeC_port1' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port1' ,'nodeB' ,'nodeB_port2' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port2' ,'nodeD' ,'nodeD_port1' FROM dual UNION ALL
SELECT 1 ,'nodeD' ,'nodeD_port1' ,'nodeC' ,'nodeC_port2' FROM dual UNION ALL
SELECT 2 ,'nodeC' ,'nodeC_port1' ,'nodeF' ,'nodeF_port1' FROM dual UNION ALL
SELECT 2 ,'nodeF' ,'nodeF_port1' ,'nodeC' ,'nodeC_port1' FROM dual),
cte ( path_id, node_1, node1_port, node_2, node2_port, lvl) AS
(SELECT path_id, node_1, node1_port, node_2, node2_port, 1
FROM tab1
WHERE node_1='nodeA' AND path_id=1
UNION ALL
SELECT t.path_id, t.node_1, t.node1_port, t.node_2, t.node2_port, lvl+1
FROM tab1 t
JOIN cte on (cte.node_2=t.node_1 AND cte.path_id=t.path_id))
SEARCH BREADTH FIRST BY lvl SET abst
CYCLE node_1 SET cycle to 'Y' default 'N'
SELECT path_id, node_1 node, node1_port node_port, rownum rn
FROM cte
WHERE cycle='N';
PATH_ID NODE NODE_PORT RN
------------------------------------
1 nodeA nodeA_port1 1
1 nodeB nodeB_port2 2
1 nodeB nodeB_port1 3
1 nodeC nodeC_port2 4
1 nodeC nodeC_port1 5
1 nodeD nodeD_port1 6
[Updated on: Fri, 11 October 2019 08:54] Report message to a moderator
|
|
|
|
Re: Oracle - path passing by multiple nodes [message #677782 is a reply to message #677781] |
Fri, 11 October 2019 09:37 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
Look in the LiveSQL for "Sorting Output: Recursive With ->Depth-First Search ->Breadth-First Search" and try it.
WITH tab1(path_id, node_1, node1_port, node_2, node2_port) AS
(SELECT 1 ,'nodeA' ,'nodeA_port1' ,'nodeB' ,'nodeB_port1' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port1' ,'nodeA' ,'nodeA_port1' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port2' ,'nodeC' ,'nodeC_port1' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port1' ,'nodeB' ,'nodeB_port2' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port2' ,'nodeD' ,'nodeD_port1' FROM dual UNION ALL
SELECT 1 ,'nodeD' ,'nodeD_port1' ,'nodeC' ,'nodeC_port2' FROM dual UNION ALL
SELECT 2 ,'nodeC' ,'nodeC_port1' ,'nodeF' ,'nodeF_port1' FROM dual UNION ALL
SELECT 2 ,'nodeF' ,'nodeF_port1' ,'nodeC' ,'nodeC_port1' FROM dual),
cte ( path_id, node_1, node1_port, node_2, node2_port, lvl) AS
(SELECT path_id, node_1, node1_port, node_2, node2_port, 1
FROM tab1
WHERE node_1='nodeA' AND path_id=1
UNION ALL
SELECT t.path_id, t.node_1, t.node1_port, t.node_2, t.node2_port, lvl+1
FROM tab1 t
JOIN cte on (cte.node_2=t.node_1 AND cte.path_id=t.path_id))
--to demonstrate different possibilities
SEARCH BREADTH FIRST BY lvl, node_1, node1_port SET abst
CYCLE node_1 SET cycle to 'Y' default 'N'
SELECT path_id, node_1 node, node1_port node_port
, lvl, abst
--to demonstrate different possibilities
, row_number() OVER (ORDER BY node_2, node2_port) orn
, rownum rn
FROM cte
WHERE cycle='N'
ORDER BY abst
;
PATH_ID NODE NODE_PORT LVL ABST ORN RN
-------------------------------------------------------------
1 nodeA nodeA_port1 1 1 2 1
1 nodeB nodeB_port1 2 2 1 2
1 nodeB nodeB_port2 2 3 4 3
1 nodeC nodeC_port1 3 5 3 4
1 nodeC nodeC_port2 3 6 6 5
1 nodeD nodeD_port1 4 9 5 6
[Updated on: Fri, 11 October 2019 09:38] Report message to a moderator
|
|
|
|
|
Re: Oracle - path passing by multiple nodes [message #677785 is a reply to message #677784] |
Fri, 11 October 2019 10:36 |
|
ghostman
Messages: 16 Registered: October 2019
|
Junior Member |
|
|
I extended the started condition with or statement just for testing purposes. But in this case the ordering will be affected:
WITH tab1(path_id, node_1, node1_port, node_2, node2_port) AS
(SELECT 1 ,'nodeA' ,'nodeA_port1' ,'nodeB' ,'nodeB_port1' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port1' ,'nodeA' ,'nodeA_port1' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port2' ,'nodeC' ,'nodeC_port1' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port1' ,'nodeB' ,'nodeB_port2' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port2' ,'nodeD' ,'nodeD_port1' FROM dual UNION ALL
SELECT 1 ,'nodeD' ,'nodeD_port1' ,'nodeC' ,'nodeC_port2' FROM dual UNION ALL
SELECT 2 ,'nodeC' ,'nodeC_port1' ,'nodeF' ,'nodeF_port1' FROM dual UNION ALL
SELECT 2 ,'nodeF' ,'nodeF_port1' ,'nodeC' ,'nodeC_port1' FROM dual),
cte ( path_id, node_1, node1_port, node_2, node2_port, lvl) AS
(SELECT path_id, node_1, node1_port, node_2, node2_port, 1
FROM tab1
WHERE (node_1='nodeA' AND path_id=1) or (node_1='nodeC' and path_id=2)
UNION ALL
SELECT t.path_id, t.node_1, t.node1_port, t.node_2, t.node2_port, lvl+1
FROM tab1 t
JOIN cte on (cte.node_2=t.node_1 AND cte.path_id=t.path_id))
--to demonstrate different possibilities
SEARCH BREADTH FIRST BY lvl, node_1, node1_port SET abst
CYCLE node_1 SET cycle to 'Y' default 'N'
SELECT path_id, node_1 node, node1_port node_port
, lvl, abst
--to demonstrate different possibilities
, row_number() OVER (ORDER BY node_2, node2_port) orn
, rownum rn
FROM cte
WHERE cycle='N'
ORDER BY path_id,abst
Output:
PATH_ID NODE NODE_PORT LVL ABST ORN RN
-----------------------------------------------------------
1 nodeA nodeA_port1 1 1 2 1
1 nodeB nodeB_port1 2 3 1 3
1 nodeB nodeB_port2 2 4 4 4
1 nodeC nodeC_port1 3 7 3 6
1 nodeC nodeC_port2 3 9 7 7
1 nodeD nodeD_port1 4 12 6 8
2 nodeC nodeC_port1 1 2 8 2
2 nodeF nodeF_port1 2 5 5 5
|
|
|
Re: Oracle - path passing by multiple nodes [message #677786 is a reply to message #677785] |
Fri, 11 October 2019 11:04 |
|
ghostman
Messages: 16 Registered: October 2019
|
Junior Member |
|
|
I added
row_number() OVER (partition by path_id ORDER BY lvl,abst)
which solved the issue.
Here is the full code:
WITH tab1(path_id, node_1, node1_port, node_2, node2_port) AS
(SELECT 1 ,'nodeC' ,'nodeC_port1' ,'nodeB' ,'nodeB_port2' FROM dual UNION ALL
SELECT 1 ,'nodeZ' ,'nodeZ_port1' ,'nodeB' ,'nodeB_port1' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port1' ,'nodeZ' ,'nodeZ_port1' FROM dual UNION ALL
SELECT 2 ,'nodeF' ,'nodeF_port1' ,'nodeC' ,'nodeC_port1' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port2' ,'nodeC' ,'nodeC_port1' FROM dual UNION ALL
SELECT 1 ,'nodeD' ,'nodeD_port1' ,'nodeC' ,'nodeC_port2' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port2' ,'nodeD' ,'nodeD_port1' FROM dual UNION ALL
SELECT 2 ,'nodeC' ,'nodeC_port1' ,'nodeF' ,'nodeF_port1' FROM dual UNION ALL
SELECT 3 ,'nodeM' ,'nodeM_port1' ,'nodeS' ,'nodeS_port2' FROM dual UNION ALL
SELECT 3 ,'nodeX' ,'nodeX_port1' ,'nodeS' ,'nodeS_port1' FROM dual UNION ALL
SELECT 3 ,'nodeS' ,'nodeS_port1' ,'nodeX' ,'nodeX_port1' FROM dual UNION ALL
SELECT 3 ,'nodeS' ,'nodeS_port2' ,'nodeM' ,'nodeM_port1' FROM dual UNION ALL
SELECT 3 ,'nodeK' ,'nodeK_port2' ,'nodeM' ,'nodeM_port2' FROM dual UNION ALL
SELECT 3 ,'nodeM' ,'nodeM_port2' ,'nodeK' ,'nodeK_port1' FROM dual
),
cte ( path_id, node_1, node1_port, node_2, node2_port, lvl) AS
(SELECT path_id, node_1, node1_port, node_2, node2_port, 1
FROM tab1
WHERE (node_1='nodeZ' AND path_id=1) or (node_1='nodeC' and path_id=2) or (node_1='nodeX' and path_id=3)
UNION ALL
SELECT t.path_id, t.node_1, t.node1_port, t.node_2, t.node2_port, lvl+1
FROM tab1 t
JOIN cte on (cte.node_2=t.node_1 AND cte.path_id=t.path_id))
SEARCH BREADTH FIRST BY lvl, node_1, node1_port SET abst
CYCLE node_1 SET cycle to 'Y' default 'N'
SELECT path_id, node_1 node, node1_port node_port
, lvl, abst
, row_number() OVER (partition by path_id ORDER BY lvl,abst) orn
FROM cte
WHERE cycle='N'
ORDER BY path_id,abst
output:
PATH_ID NODE NODE_PORT LVL ABST ORN
-----------------------------------------------------------
1 nodeZ nodeZ_port1 1 3 1
1 nodeB nodeB_port1 2 4 2
1 nodeB nodeB_port2 2 5 3
1 nodeC nodeC_port1 3 9 4
1 nodeC nodeC_port2 3 11 5
1 nodeD nodeD_port1 4 18 6
2 nodeC nodeC_port1 1 1 1
2 nodeF nodeF_port1 2 6 2
3 nodeX nodeX_port1 1 2 1
3 nodeS nodeS_port1 2 7 2
3 nodeS nodeS_port2 2 8 3
3 nodeM nodeM_port1 3 12 4
3 nodeM nodeM_port2 3 13 5
3 nodeK nodeK_port2 4 19 6
Thanks for your help
[Updated on: Fri, 11 October 2019 11:05] Report message to a moderator
|
|
|
Re: Oracle - path passing by multiple nodes [message #677821 is a reply to message #677784] |
Mon, 14 October 2019 02:59 |
|
ghostman
Messages: 16 Registered: October 2019
|
Junior Member |
|
|
it turned out that sorting is still not totally correct :
WITH tab1(path_id, node_1, node1_port, node_2, node2_port) AS
(SELECT 1 ,'nodeZ' ,'nodeZ_port1' ,'nodeB' ,'nodeB_port5' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port5' ,'nodeZ' ,'nodeZ_port1' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port2' ,'nodeC' ,'nodeC_port1' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port1' ,'nodeB' ,'nodeB_port2' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port2' ,'nodeD' ,'nodeD_port1' FROM dual UNION ALL
SELECT 1 ,'nodeD' ,'nodeD_port1' ,'nodeC' ,'nodeC_port2' FROM dual UNION ALL
SELECT 2 ,'nodeF' ,'nodeF_port1' ,'nodeC' ,'nodeC_port1' FROM dual UNION ALL
SELECT 2 ,'nodeC' ,'nodeC_port1' ,'nodeF' ,'nodeF_port1' FROM dual UNION ALL
SELECT 3 ,'nodeM' ,'nodeM_port1' ,'nodeS' ,'nodeS_port2' FROM dual UNION ALL
SELECT 3 ,'nodeX' ,'nodeX_port1' ,'nodeS' ,'nodeS_port1' FROM dual UNION ALL
SELECT 3 ,'nodeS' ,'nodeS_port1' ,'nodeX' ,'nodeX_port1' FROM dual UNION ALL
SELECT 3 ,'nodeS' ,'nodeS_port2' ,'nodeM' ,'nodeM_port1' FROM dual UNION ALL
SELECT 3 ,'nodeK' ,'nodeK_port2' ,'nodeM' ,'nodeM_port2' FROM dual UNION ALL
SELECT 3 ,'nodeM' ,'nodeM_port2' ,'nodeK' ,'nodeK_port1' FROM dual
),
cte ( path_id, node_1, node1_port, node_2, node2_port, lvl) AS
(SELECT path_id, node_1, node1_port, node_2, node2_port, 1
FROM tab1
WHERE (node_1='nodeZ' AND path_id=1) or (node_1='nodeC' and path_id=2) or (node_1='nodeX' and path_id=3)
UNION ALL
SELECT t.path_id, t.node_1, t.node1_port, t.node_2, t.node2_port, lvl+1
FROM tab1 t
JOIN cte on (cte.node_2=t.node_1 AND cte.path_id=t.path_id))
SEARCH BREADTH FIRST BY lvl, node_1 SET abst
CYCLE node_1 SET cycle to 'Y' default 'N'
SELECT path_id, node_1 node, node1_port node_port
, lvl, abst
, row_number() OVER (partition by path_id ORDER BY lvl,abst) orn
FROM cte
WHERE cycle='N'
ORDER BY path_id,ORN
As you can see nodeB_port5 must be before nodeB_port2 , yet nodeB_port2 is coming first , as it's sorted alphabetically.
PATH_ID NODE NODE_PORT LVL ABST ORN
1 nodeZ nodeZ_port1 1 3 1
1 nodeB nodeB_port2 2 4 2
1 nodeB nodeB_port5 2 5 3
1 nodeC nodeC_port2 3 9 4
1 nodeC nodeC_port1 3 11 5
1 nodeD nodeD_port1 4 18 6
2 nodeC nodeC_port1 1 1 1
2 nodeF nodeF_port1 2 6 2
3 nodeX nodeX_port1 1 2 1
3 nodeS nodeS_port2 2 7 2
3 nodeS nodeS_port1 2 8 3
3 nodeM nodeM_port2 3 12 4
3 nodeM nodeM_port1 3 13 5
3 nodeK nodeK_port2 4 19 6
How to solve this issue ?
|
|
|
Re: Oracle - path passing by multiple nodes [message #677827 is a reply to message #677821] |
Mon, 14 October 2019 10:14 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
If I understand the logic right, you could notice and compare the previous NODE_1 (pnode_1) with the actual node_2. But then nodeC_port2
and nodeC_port1 has to change too?
WITH tab1(path_id, node_1, node1_port, node_2, node2_port) AS
(SELECT 1 ,'nodeZ' ,'nodeZ_port1' ,'nodeB' ,'nodeB_port5' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port5' ,'nodeZ' ,'nodeZ_port1' FROM dual UNION ALL
SELECT 1 ,'nodeB' ,'nodeB_port2' ,'nodeC' ,'nodeC_port1' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port1' ,'nodeB' ,'nodeB_port2' FROM dual UNION ALL
SELECT 1 ,'nodeC' ,'nodeC_port2' ,'nodeD' ,'nodeD_port1' FROM dual UNION ALL
SELECT 1 ,'nodeD' ,'nodeD_port1' ,'nodeC' ,'nodeC_port2' FROM dual UNION ALL
SELECT 2 ,'nodeF' ,'nodeF_port1' ,'nodeC' ,'nodeC_port1' FROM dual UNION ALL
SELECT 2 ,'nodeC' ,'nodeC_port1' ,'nodeF' ,'nodeF_port1' FROM dual UNION ALL
SELECT 3 ,'nodeM' ,'nodeM_port1' ,'nodeS' ,'nodeS_port2' FROM dual UNION ALL
SELECT 3 ,'nodeX' ,'nodeX_port1' ,'nodeS' ,'nodeS_port1' FROM dual UNION ALL
SELECT 3 ,'nodeS' ,'nodeS_port1' ,'nodeX' ,'nodeX_port1' FROM dual UNION ALL
SELECT 3 ,'nodeS' ,'nodeS_port2' ,'nodeM' ,'nodeM_port1' FROM dual UNION ALL
SELECT 3 ,'nodeK' ,'nodeK_port2' ,'nodeM' ,'nodeM_port2' FROM dual UNION ALL
SELECT 3 ,'nodeM' ,'nodeM_port2' ,'nodeK' ,'nodeK_port1' FROM dual
),
cte ( path_id, node_1, node1_port, node_2, node2_port, pnode_1 , lvl) AS
(SELECT path_id, node_1, node1_port, node_2, node2_port, CAST (NULL AS VARCHAR2(50)), 1
FROM tab1
WHERE (node_1='nodeZ' AND path_id=1) or (node_1='nodeC' and path_id=2) or (node_1='nodeX' and path_id=3)
UNION ALL
SELECT t.path_id, t.node_1, t.node1_port, t.node_2, t.node2_port, cte.node_1, lvl+1
FROM tab1 t
JOIN cte on (cte.node_2=t.node_1 AND cte.path_id=t.path_id))
--SEARCH BREADTH FIRST BY lvl SET abst
CYCLE node_1 SET cycle to 'Y' default 'N'
SELECT path_id, node_1 node, node1_port node_port, node_2, pnode_1
, lvl
--, abst
--, row_number() OVER (partition by path_id ORDER BY lvl,abst) orn
, decode(node_2, pnode_1, 0, 1) zsort
FROM cte
WHERE cycle='N'
ORDER BY path_id, lvl, zsort;
PATH_ID NODE NODE_PORT NODE_2 PNODE_1 LVL ZSORT
-----------------------------------------------------------------
1 nodeZ nodeZ_port1 nodeB 1 1
1 nodeB nodeB_port5 nodeZ nodeZ 2 0
1 nodeB nodeB_port2 nodeC nodeZ 2 1
1 nodeC nodeC_port1 nodeB nodeB 3 0
1 nodeC nodeC_port2 nodeD nodeB 3 1
1 nodeD nodeD_port1 nodeC nodeC 4 0
2 nodeC nodeC_port1 nodeF 1 1
2 nodeF nodeF_port1 nodeC nodeC 2 0
3 nodeX nodeX_port1 nodeS 1 1
3 nodeS nodeS_port1 nodeX nodeX 2 0
3 nodeS nodeS_port2 nodeM nodeX 2 1
3 nodeM nodeM_port1 nodeS nodeS 3 0
3 nodeM nodeM_port2 nodeK nodeS 3 1
3 nodeK nodeK_port2 nodeM nodeM 4 0
|
|
|
|
Goto Forum:
Current Time: Sat Jun 15 16:30:46 CDT 2024
|