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 Go to next message
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 #677744 is a reply to message #677742] Thu, 10 October 2019 09:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you post a working Test case: create table and insert statements along with the result you want with these data then we will be able to work with your table and data.

Re: Oracle - path passing by multiple nodes [message #677749 is a reply to message #677744] Fri, 11 October 2019 01:23 Go to previous messageGo to next message
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 #677754 is a reply to message #677749] Fri, 11 October 2019 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your data and your result mismatch. For instance, there is no nodeA in your data.
This is consistent with the fact that your original post mentions, for example, "node_1_port" which does not exits in the tables, or "start from node1 and end in node 4 (as per table2)" where there is no "node1" or "node 4" in the data...

Re: Oracle - path passing by multiple nodes [message #677756 is a reply to message #677754] Fri, 11 October 2019 04:18 Go to previous messageGo to next message
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 Go to previous messageGo to next message
_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 #677771 is a reply to message #677765] Fri, 11 October 2019 07:09 Go to previous messageGo to next message
ghostman
Messages: 16
Registered: October 2019
Junior Member
we cannot depend on the node naming as they are not ordered alphabetically, nodeA,nodeB... are used to simplify the example.
Re: Oracle - path passing by multiple nodes [message #677776 is a reply to message #677771] Fri, 11 October 2019 07:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your code there are rules you didn't specify.
For instance, what is the underlying rule for "node_2!=nodes(cnt-2)".
If you hide some rules, we can't provide accurate code.

In addition, there are cycles in your data:
nodeA -> nodeB -> nodeA -> nodeB -> nodeA -> nodeB -> ...
nodeA -> nodeB -> nodeC -> nodeB -> nodeC -> nodeB -> nodeC -> ...
nodeA -> nodeB -> nodeC -> nodeD -> nodeC -> nodeD -> nodeC -> nodeD -> ...

Re: Oracle - path passing by multiple nodes [message #677778 is a reply to message #677776] Fri, 11 October 2019 08:15 Go to previous messageGo to next message
ghostman
Messages: 16
Registered: October 2019
Junior Member
I put this condition "node_2!=nodes(cnt-2)" to eliminate the duplication existing in the data , as the hops existing in the data are reciprocal. For instance :
1 nodeA nodeA_port1 nodeB nodeB_port1
1 nodeB nodeB_port1 nodeA nodeA_port1

the cycles you mentioned are due to these duplication that must be ignored.
the logic to get the hops order of each path is :
(start_node + start_node_port) --> [(node_2 + node2_port) where node_1=start_node] as second_node + second_node_port1] --> [(node_2 + node2_port) where node_1=second_node and node_2 not in previous detected hops] and etc...
Also as you can see each interconnect node has 2 ports (except start and end nodes having only port) where first port is connected to the previous node and the second port is connected to the next node. So in order to include both ports in the output I used the cnt1 and cnt_or variables.
Re: Oracle - path passing by multiple nodes [message #677779 is a reply to message #677778] Fri, 11 October 2019 08:52 Go to previous messageGo to next message
_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 #677781 is a reply to message #677779] Fri, 11 October 2019 09:17 Go to previous messageGo to next message
ghostman
Messages: 16
Registered: October 2019
Junior Member
Thank you for your answer , this looks a lot less complex than my script.

The ports order in each node are always reversed , for instance nodeB_port1 must be before nodeB_port2 , how to rectify that?
Do I have to use a loop to iterate on all path_ids , or it can be done in a single query ?

Re: Oracle - path passing by multiple nodes [message #677782 is a reply to message #677781] Fri, 11 October 2019 09:37 Go to previous messageGo to next message
_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 #677783 is a reply to message #677782] Fri, 11 October 2019 10:06 Go to previous messageGo to next message
ghostman
Messages: 16
Registered: October 2019
Junior Member
Thank you , it is clear now.
How to proceed with our path_ids? my table contains around 100k path_id. Am I forced to iterate on them one by one ?
Re: Oracle - path passing by multiple nodes [message #677784 is a reply to message #677783] Fri, 11 October 2019 10:17 Go to previous messageGo to next message
_jum
Messages: 577
Registered: February 2008
Senior Member
No, the JOIN condition

...
 JOIN cte on (cte.node_2=t.node_1 AND cte.path_id=t.path_id))
...

secures, that the path_id doesn't change in the CTE.

You only have to extend the start condition (as SUBQUERY or JOIN)
...
   WHERE node_1='nodeA' AND path_id=1
...

[Updated on: Fri, 11 October 2019 10:18]

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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Smile

[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 Go to previous messageGo to next message
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 ? Sad
Re: Oracle - path passing by multiple nodes [message #677827 is a reply to message #677821] Mon, 14 October 2019 10:14 Go to previous messageGo to next message
_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

Re: Oracle - path passing by multiple nodes [message #677835 is a reply to message #677827] Tue, 15 October 2019 02:21 Go to previous message
ghostman
Messages: 16
Registered: October 2019
Junior Member
this works perfectly!
nodeC_port1 comes before nodeC_port2 (as it is now in your query) as it's reached first in the path.
Previous Topic: ORA-02315: Incorrect number of arguments for default constructor
Next Topic: Why calculated a later invalid statement in a case-when after first true clause found?
Goto Forum:
  


Current Time: Thu Mar 28 10:22:43 CDT 2024