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.
] 
Blog
Search
Help
Members
Register
Login
Home
