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 TriedI 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
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...