Oracle Hierarchical Query on Linked List Table

Question

I have the following table that implements a linked list. I want to query starting with rate_sequence_id of 1, and get the next record linked to it, which is 30 in this case. So the query should return two rows (rate_sequence_id of 1, and 30).

However, the following query either returns only 1 row, or a "Loop" error, depending on which column comes first in the CONNECT BY clause.

Is what I am trying to do possible with this data?

SELECT * FROM TEST
CONNECT BY PRIOR RATE_SEQUENCE_ID = NEXT_RATE
START WITH RATE_SEQUENCE_ID = 1

Test Data:

CREATE TABLE TEST (
  RATE_SEQUENCE_ID      NUMBER(10,0),
  NEXT_RATE             NUMBER(10,0),
  DURATION              NUMBER,
  RATE                  NUMBER
);  


Insert into TEST (RATE_SEQUENCE_ID,NEXT_RATE,DURATION,RATE) values (50,51,28,0.99);
Insert into TEST (RATE_SEQUENCE_ID,NEXT_RATE,DURATION,RATE) values (51,51,112,9.99);
Insert into TEST (RATE_SEQUENCE_ID,NEXT_RATE,DURATION,RATE) values (1,30,28,0.99);
Insert into TEST (RATE_SEQUENCE_ID,NEXT_RATE,DURATION,RATE) values (30,30,112,14.99);
Insert into TEST (RATE_SEQUENCE_ID,NEXT_RATE,DURATION,RATE) values (0,0,0,0);
Insert into TEST (RATE_SEQUENCE_ID,NEXT_RATE,DURATION,RATE) values (31,0,30,0);

Which version of the database? Hierarchical query is an area where Oracle have added lots of functionality in later releases. — APC
– APC, Commented Apr 12, 2013 at 13:36

APC · Accepted Answer · 2013-04-12 13:38:48Z

1

So this is your problem:

Insert into TEST (RATE_SEQUENCE_ID,NEXT_RATE,DURATION,RATE) values (30,30,112,14.99);

30 is connected to itself. Is that right or is that a typo?

If that data is correct and you're using a modern version of Oracle you can use the NOCYCLE clause to escape from the loop. This is definitely in 10g (and maybe in 9i, my memory fails me here). Find out more.

SELECT * FROM TEST
CONNECT BY NOCYCLE PRIOR RATE_SEQUENCE_ID = NEXT_RATE
START WITH RATE_SEQUENCE_ID = 1

answered Apr 12, 2013 at 13:38

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

EdgeCase Over a year ago

Not a typo. That means that the record is terminal. But thanks, that is very helpful advice. We're using 11G

Collectives™ on Stack Overflow

Oracle Hierarchical Query on Linked List Table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related