Home » Server Options » Replication » Help In hierarchy query
Help In hierarchy query [message #681748] Tue, 25 August 2020 14:59 Go to next message
purnima1
Messages: 79
Registered: June 2014
Member
Hi All,
I need to understand the output of following code
CREATE TABLE TEST_LOOP
(ID1 NUMBER,
ID2 NUMBER);


INSERT INTO TEST_LOOP VALUES (1,2);
INSERT INTO TEST_LOOP VALUES (2,2);


INSERT INTO TEST_LOOP VALUES (3,4);
INSERT INTO TEST_LOOP VALUES (4,3);

INSERT INTO TEST_LOOP VALUES (5,6);
INSERT INTO TEST_LOOP VALUES (6,7);
INSERT INTO TEST_LOOP VALUES (7,7);

INSERT INTO TEST_LOOP VALUES (9,10);
INSERT INTO TEST_LOOP VALUES (10,11);
INSERT INTO TEST_LOOP VALUES (11,12);
INSERT INTO TEST_LOOP VALUES (12,12);

COMMIT;


SELECT id1,id2,PRIOR id2 ,CONNECT_BY_ISCYCLE loop
FROM TEST_LOOP
START WITH id1 in (1,3,5,9)
CONNECT BY nocycle    PRIOR id2=id1;


ID1 ID2 PRIORID2 LOOP
3 4 0
4 3 4 1
5 6 0
6 7 6 1
9 10 0
10 11 10 0
11 12 11 1

[/output]

What I am trying to understand why I am not getting following rows :
2,2
7,7
12,12

and what do I need to do in order to bring it
Please help .

[Updated on: Tue, 25 August 2020 15:05]

Report message to a moderator

Re: Help In hierarchy query [message #681749 is a reply to message #681748] Tue, 25 August 2020 15:05 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
Is there way to show the result in better format ?
Re: Help In hierarchy query [message #681750 is a reply to message #681749] Wed, 26 August 2020 00:23 Go to previous messageGo to next message
Michel Cadot
Messages: 67540
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Thu, 06 December 2018 20:55

Quote:
You neglected to provide even the most basic details like OS name & version along with Oracle version to 4 decimal places.
You have not posted any SQL & results that show any "issue" exists.
You did not answer to our questions in your previous topics.
You did not feedback in your previous topics.
You did not post the solution you found in your previous topics.
You did not thank people who spent time to help you in your previous topics.
You posted this in the wrong forum, how is this a "security" question?
...
How is this a "Replication" question?

Re: Help In hierarchy query [message #681755 is a reply to message #681750] Wed, 26 August 2020 11:22 Go to previous messageGo to next message
purnima1
Messages: 79
Registered: June 2014
Member
Hi Michel,
For the current question After raising it under replication by mistake I was looking for option to change it . But I am not getting it.

There are certain questions for which I did not get response and that is still not resolved till date.
Re: Help In hierarchy query [message #681805 is a reply to message #681755] Tue, 01 September 2020 08:08 Go to previous message
_jum
Messages: 573
Registered: February 2008
Senior Member
Hi purnima1,

Quote:
What I am trying to understand why I am not getting following rows :
2,2
7,7
12,12
These rows you "hide" with the NOCYCLE-clause.

Compare the result if you avoid the cycles by yourself (with: AND PRIOR id1 != id2):

   SELECT id1,id2,PRIOR id2, level lv
     -- , CONNECT_BY_ISCYCLE loop
     FROM TEST_LOOP
    START WITH id1 in (1,3,5,9)
  CONNECT BY --NOCYCLE
    PRIOR id2  = id1 
AND PRIOR id1 != id2;
ID1   ID2  PRIORID2   LV
-------------------------------
1	2		1
2	2	2	2
3	4		1
5	6		1
6	7	6	2
7	7	7	3
9	10		1
10	11	10	2
11	12	11	3
12	12	12	4

[Updated on: Tue, 01 September 2020 08:09]

Report message to a moderator

Previous Topic: Apply process error
Goto Forum:
  


Current Time: Wed Nov 25 10:50:59 CST 2020