Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchical Query (merged) (Oracle 10g)
Hierarchical Query (merged) [message #650537] Wed, 27 April 2016 05:58 Go to next message
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 #650543 is a reply to message #650537] Wed, 27 April 2016 06:40 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Could anyone please help in giving inputs to achieve the desired output.


Which is? Show us the result for the data you gave.

What is the purpose of ID column in PARENT_CHILD? Is it always set to 1?

Re: Hierarchical Query (merged) [message #650565 is a reply to message #650537] Wed, 27 April 2016 13:01 Go to previous messageGo to next message
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 #650570 is a reply to message #650565] Wed, 27 April 2016 13:52 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
martijn wrote on Wed, 27 April 2016 11:01
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


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
Re: Hierarchical Query (merged) [message #650576 is a reply to message #650537] Wed, 27 April 2016 20:52 Go to previous messageGo to next message
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 Go to previous messageGo to next message
martijn
Messages: 286
Registered: December 2006
Location: Netherlands
Senior Member
BlackSwan wrote on Wed, 27 April 2016 20:52
martijn wrote on Wed, 27 April 2016 11:01
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


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 Go to previous messageGo to next message
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
Re: Hierarchical Query (merged) [message #650628 is a reply to message #650623] Thu, 28 April 2016 07:51 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There is no possible dynamic column number in SQL.

Previous Topic: Stale Materialised view
Next Topic: Index on composite foreign key
Goto Forum:
  


Current Time: Thu Mar 28 12:55:27 CDT 2024