Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Silpa.

Asked: December 18, 2012 - 12:22 pm UTC

Last updated: December 18, 2012 - 1:43 pm UTC

Version: 11.2.0

Viewed 1000+ times

You Asked

Hello there,

I am trying to figure a way to implement bi-directional fetch using hierarchical query.

Table –
CREATE TABLE DISA_OWNER.NETWORK_TABLE
(
  ORIG_CODE  NUMBER,
  DEST_CODE  NUMBER
);


I have my data in the below format

ORIGIN DEST
11 12
12 13
14 11
14 15
14 16
14 17
18 11
19 110
111 112

INSERT INTO network_table
VALUES(11, 12);

INSERT INTO network_table
VALUES(12, 13);

INSERT INTO network_table
VALUES(14, 11);

INSERT INTO network_table
VALUES(14, 15);

INSERT INTO network_table
VALUES(14, 16);

INSERT INTO network_table
VALUES(14, 17);

INSERT INTO network_table
VALUES(18, 11);

INSERT INTO network_table
VALUES(19, 110);

INSERT INTO network_table
VALUES(111, 112);


Requirement:
I need to pull the network for a particular node irrespective whether it is an origin or destination (in both directions for each node further discovered).
For suppose, I want to find out the network for node 11 then I will start with 11 as origin which gives me,

11 12
12 13

Then find out the network for node A1 as destination which should include –
14 11
14 15
14 16
14 17
18 11

So, my final output should be –
11 12
12 13
14 11
14 15
14 16
14 17
18 11

Trails Tried
I have tried using hierarchical queries. I was able to fetch network where 11 is origin or destination using –

SELECT DISTINCT orig_code, dest_code
FROM network_table
START WITH (orig_code = 11 OR dest_code = 11)
CONNECT BY  PRIOR dest_code =  orig_code
And it gave me below output – 


And below query

SELECT DISTINCT orig_code, dest_code
FROM network_table
START WITH (orig_code = 11 OR dest_code = 11)
CONNECT BY   dest_code =  PRIOR orig_code


As you could see, the network never included the path for 14 and 18 nodes.
Is there a way to achieve the required output?

Your input is much appreciated.

Thanks, Silpa


and Tom said...

I think you might want this:

ops$tkyte%ORA11GR2> column o format a20
ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> variable n number
ops$tkyte%ORA11GR2> exec :n := 11

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select rpad('*',2*level,'*') || orig_code o, dest_code
  2    from network_table
  3   start with orig_code = :n
  4  connect by prior dest_code = orig_code
  5  union all
  6  select rpad('*',2*level,'*') || orig_code o, dest_code
  7    from network_table
  8   start with orig_code in ( select orig_code from network_table where dest_code = :n )
  9  connect by prior dest_code = orig_code and orig_code <> :n
 10  /

O                     DEST_CODE
-------------------- ----------
**11                         12
****12                       13
**14                         11
**14                         15
**14                         16
**14                         17
**18                         11

7 rows selected.




I base that more on your output than the explanation. It is a hunch...

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Unlimited bi-directional fetch

Silpa, December 18, 2012 - 4:25 pm UTC

Thanks for the response!

For the given data, this is precisely what I am looking for but there is a problem.

This works for 2 levels in both directions. suppose there is another record 23=>15 and 23=>24

INSERT INTO network_table
VALUES ( 23, 15)

INSERT INTO network_table
VALUES ( 23, 24)

I would expect both the records as well in the output but with this query its skipped. And my requirement is unlimited fetch in both directions.

Do you think its possible?


A method using recursive subquery factoring

Kim Berg Hansen, December 19, 2012 - 3:09 am UTC

Hi, Silpa

As you are on 11.2, there is a possibility using recursive subquery factoring like this:

SQL> with net (orig_code, dest_code, orig_recur, dest_recur, lvl)
  2  as (
  3     select n.orig_code
  4          , n.dest_code
  5          , 11 orig_recur
  6          , case n.orig_code
  7               when 11 then dest_code
  8                       else orig_code
  9            end dest_recur
 10          , 1 lvl
 11       from network_table n
 12      where n.orig_code = 11
 13         or n.dest_code = 11
 14     union all
 15     select n.orig_code
 16          , n.dest_code
 17          , net.dest_recur orig_recur
 18          , case n.orig_code
 19               when net.dest_recur then n.dest_code
 20                                   else n.orig_code
 21            end dest_recur
 22          , net.lvl + 1 lvl
 23       from net
 24       join network_table n
 25            on (n.orig_code = net.dest_recur and n.dest_code != net.orig_recur)
 26            or (n.dest_code = net.dest_recur and n.orig_code != net.orig_recur)
 27     )
 28     search breadth first by orig_recur, dest_recur set ordering
 29  select orig_code
 30       , dest_code
 31       , orig_recur
 32       , dest_recur
 33       , lvl
 34    from net
 35   order by ordering
 36  /

 ORIG_CODE  DEST_CODE ORIG_RECUR DEST_RECUR        LVL
---------- ---------- ---------- ---------- ----------
        11         12         11         12          1
        14         11         11         14          1
        18         11         11         18          1
        12         13         12         13          2
        14         15         14         15          2
        14         16         14         16          2
        14         17         14         17          2
        23         15         15         23          3
        23         24         23         24          4

9 rows selected.


We want to traverse the network no matter the direction of the connections between nodes. So what we do in the code is to always consider the start to be "origin recursive" and the other end of the connection to be "destination recursive" no matter which node originally is defined as origin or destination.

That way level 1 becomes the three connections from or to node 11, but all three get orig_recur = 11 and dest_recur = "the other node." Then the recursion finds the next level by getting nodes that have either orig_code or dest_code = dest_recur (and do not "double back" from where we came in the graph.) Again we set orig_recur to "where we came from" and dest_recur to "the other node." And so on, and so on.

I have tried to make a similar technique using connect by, but in a connect by I can't do a "prior dest_recur" or "prior orig_recur" as prior cannot be used on column aliases. That is one place where recursive subquery factoring can do something that connect by cannot ;-)

Thank You

Silpa, December 19, 2012 - 11:20 am UTC

Thanks alot!

It worked perfectly. I have included CYCLE clause to restrict infinite destination recursion.

Thanks again.

This is awesome

Tom N, December 20, 2012 - 8:37 am UTC

I love coming here. I learn something new every day!!! I can't wait to find a problem looking for this solution :)

Great!

Pavle Gartner, January 08, 2015 - 10:35 am UTC

Thanks for this code snippet, I struggled 2 days with connect by prior - this is just what I needed!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library