Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical Query (merged) (Oracle 10g)
Hierarchical Query (merged) [message #650537] |
Wed, 27 April 2016 05:58 |
lokimisc
Messages: 101 Registered: February 2008
|
Senior Member |
|
|
Hello All,
I have requirement in Hierarchy concept. i.e if any node name is given then
1. Function has to traverse backward (path) to find the root node of given node.
2. Function has to traverse forward (path) to find the leaf node of given node.
3. To convert rows into columns based on levels dynamically.
Also please note that each node is having multiple parents and multiple childrens
Please check sample data
CREATE TABLE MASTER
(
ID NUMBER,
NAME VARCHAR2(50),
TYPE VARCHAR2(20)
);
INSERT INTO MASTER VALUES(1,'A', 'JSP');
INSERT INTO MASTER VALUES(2,'B', 'RULE');
INSERT INTO MASTER VALUES(3,'C', 'FUNCTION');
INSERT INTO MASTER VALUES(4,'D', 'FUNCTION');
INSERT INTO MASTER VALUES(5,'E', 'RULE');
INSERT INTO MASTER VALUES(6,'F', 'FUNCTION');
INSERT INTO MASTER VALUES(7,'G', 'FUNCTION');
INSERT INTO MASTER VALUES(8,'H', 'FUNCTION');
INSERT INTO MASTER VALUES(9,'I', 'RULE');
INSERT INTO MASTER VALUES(10,'J', 'FUNCTION');
INSERT INTO MASTER VALUES(11,'K', 'FUNCTION');
INSERT INTO MASTER VALUES(12,'L', 'RULE');
INSERT INTO MASTER VALUES(13,'M', 'FUNCTION');
INSERT INTO MASTER VALUES(14,'N', 'RULE');
INSERT INTO MASTER VALUES(15,'O', 'FUNCTION');
INSERT INTO MASTER VALUES(16,'P', 'JSP');
CREATE TABLE PARENT_CHILD
(
ID NUMBER,
Parent_id NUMBER,
Child_id NUMBER
);
INSERT INTO parent_child VALUES (1,1,2);
INSERT INTO parent_child VALUES (1,1,3);
INSERT INTO parent_child VALUES (1,3,16);
INSERT INTO parent_child VALUES (1,3,4);
INSERT INTO parent_child VALUES (1,3,5);
INSERT INTO parent_child VALUES (1,3,6);
INSERT INTO parent_child VALUES (1,8,4);
INSERT INTO parent_child VALUES (1,4,7);
INSERT INTO parent_child VALUES (1,7,9);
INSERT INTO parent_child VALUES (1,7,10);
INSERT INTO parent_child VALUES (1,11,7);
INSERT INTO parent_child VALUES (1,12,11);
INSERT INTO parent_child VALUES (1,11,13);
INSERT INTO parent_child VALUES (1,11,14);
INSERT INTO parent_child VALUES (1,13,15);
Example:
If G is given has input parameter then
output should return
G -> D -> C -> A ( This is backward traversing)
G -> D -> C -> P ( This is backward traversing)
G -> J -> M -> O ( This is forward traversing)
Could anyone please help in giving inputs to achieve the desired output.
Also please let me know, if you need anymore info.
Regards,
Lokesh
|
|
|
|
Re: Hierarchical Query (merged) [message #650565 is a reply to message #650537] |
Wed, 27 April 2016 13:01 |
martijn
Messages: 286 Registered: December 2006 Location: Netherlands
|
Senior Member |
|
|
Actually I should wait for the answers you supply to the questions of Michel Cadot.
However....I see recursion...And I'm loving that:
Piece of pseudo-code(backwards traversal):
function recurs(p)
{
select * from parent_child where child=p
loop
v=parent
recurs(v)
done
I'm quite sure that hierarchical queries can solve this within sql.....but I'm not to strong with hierarchical sql
|
|
|
|
Re: Hierarchical Query (merged) [message #650576 is a reply to message #650537] |
Wed, 27 April 2016 20:52 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
Your desired output and explanation don't seem to match your sample data.
Using your given sample data:
SCOTT@orcl> COLUMN name FORMAT A30
SCOTT@orcl> SELECT * FROM master
2 /
ID NAME TYPE
---------- ------------------------------ --------------------
1 A JSP
2 B RULE
3 C FUNCTION
4 D FUNCTION
5 E RULE
6 F FUNCTION
7 G FUNCTION
8 H FUNCTION
9 I RULE
10 J FUNCTION
11 K FUNCTION
12 L RULE
13 M FUNCTION
14 N RULE
15 O FUNCTION
16 P JSP
16 rows selected.
SCOTT@orcl> SELECT * FROM parent_child
2 /
ID PARENT_ID CHILD_ID
---------- ---------- ----------
1 1 2
1 1 3
1 3 16
1 3 4
1 3 5
1 3 6
1 8 4
1 4 7
1 7 9
1 7 10
1 11 7
1 12 11
1 11 13
1 11 14
1 13 15
15 rows selected.
What you asked for would be something like this:
SCOTT@orcl> WITH
2 scbps AS
3 (SELECT SUBSTR (SYS_CONNECT_BY_PATH (name, ' -> '), 5) scbp
4 FROM parent_child, master
5 WHERE parent_child.parent_id = master.id
6 START WITH name = 'G'
7 CONNECT BY PRIOR parent_id = child_id
8 UNION
9 SELECT SUBSTR (SYS_CONNECT_BY_PATH (name, ' -> '), 5) scbp
10 FROM parent_child, master
11 WHERE parent_child.child_id = master.id
12 START WITH name = 'G'
13 CONNECT BY PRIOR child_id = parent_id)
14 SELECT t1.scbp
15 FROM scbps t1
16 WHERE NOT EXISTS
17 (SELECT t2.scbp
18 FROM scbps t2
19 WHERE t1.scbp != t2.scbp
20 AND INSTR (t2.scbp, t1.scbp) > 0)
21 /
SCBP
--------------------------------------------------------------------------------
G -> D -> C -> A
G -> D -> H
G -> I
G -> J
G -> K -> L
5 rows selected.
If you were to modify your sample data, like so:
SCOTT@orcl> SELECT * FROM parent_child
2 /
ID PARENT_ID CHILD_ID
---------- ---------- ----------
1 1 2
1 1 3
1 3 4
1 3 5
1 3 6
1 4 7
1 7 10
1 12 11
1 11 13
1 11 14
1 13 15
1 16 3
1 10 13
13 rows selected.
then you would get the desired output:
SCOTT@orcl> WITH
2 scbps AS
3 (SELECT SUBSTR (SYS_CONNECT_BY_PATH (name, ' -> '), 5) scbp
4 FROM parent_child, master
5 WHERE parent_child.parent_id = master.id
6 START WITH name = 'G'
7 CONNECT BY PRIOR parent_id = child_id
8 UNION
9 SELECT SUBSTR (SYS_CONNECT_BY_PATH (name, ' -> '), 5) scbp
10 FROM parent_child, master
11 WHERE parent_child.child_id = master.id
12 START WITH name = 'G'
13 CONNECT BY PRIOR child_id = parent_id)
14 SELECT t1.scbp
15 FROM scbps t1
16 WHERE NOT EXISTS
17 (SELECT t2.scbp
18 FROM scbps t2
19 WHERE t1.scbp != t2.scbp
20 AND INSTR (t2.scbp, t1.scbp) > 0)
21 /
SCBP
--------------------------------------------------------------------------------
G -> D -> C -> A
G -> D -> C -> P
G -> J -> M -> O
3 rows selected.
|
|
|
Re: Hierarchical Query (merged) [message #650582 is a reply to message #650570] |
Thu, 28 April 2016 00:38 |
martijn
Messages: 286 Registered: December 2006 Location: Netherlands
|
Senior Member |
|
|
BlackSwan wrote on Wed, 27 April 2016 20:52martijn wrote on Wed, 27 April 2016 11:01Actually I should wait for the answers you supply to the questions of Michel Cadot.
However....I see recursion...And I'm loving that:
Piece of pseudo-code(backwards traversal):
function recurs(p)
{
select * from parent_child where child=p
loop
v=parent
recurs(v)
done
I'm quite sure that hierarchical queries can solve this within sql.....but I'm not to strong with hierarchical sql
nice job of coding infinite loop.
LOOP has no way to exit, terminate or stop
With PL/SQL all variables must be defined before they ca be used.
What is "v"?
SELECT inside PL/SQL must include INTO clause
uh....all points taken. However, I thought that the word "pseudo-code" would give away thtat this is not actual code. I wrote it to give an idea for a possibility.
|
|
|
Re: Hierarchical Query (merged) [message #650623 is a reply to message #650582] |
Thu, 28 April 2016 07:34 |
lokimisc
Messages: 101 Registered: February 2008
|
Senior Member |
|
|
I'm very thankful for all those who are giving inputs to get required output.
My apology to all for adding bad data in parent_child table i.e Id column will have sequential unique value instead of 1.
Basically I need 2 function or query, first one which gives backward traversing output and another one forward traversing output. Based on number of nodes while traversing output should dynamically converts rows into columns as shown in sample output.
Please let me know, if more info is required
Sample Output:
Column1 Column2 Column3 Column4 ..... Column N
G D C A ..... Root_Node1
G D C P ..... Root_Node2
G J M O ..... Leaf_Node1
Thanking You.
Regards,
Lokesh
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 12:55:27 CDT 2024
|