| Connect By Prior and nocycles ... [message #352828] |
Thu, 09 October 2008 16:24  |
chlamy
Messages: 2
Registered: October 2008
|
Junior Member |
|
|
Hi everyone,
I was playing with Oracles recursive capabilities Connect By and discovered, that cycle-free is not quite true. I have the following data (simple example):
create table edge (from_node integer, to_node integer);
INSERT INTO edge VALUES ('1', '2');
INSERT INTO edge VALUES ('1', '4');
INSERT INTO edge VALUES ('4', '9');
INSERT INTO edge VALUES ('9', '14');
INSERT INTO edge VALUES ('14', '1');
I use the following query:
SELECT 1 as start_node, to_node AS end_node, level AS path_length
FROM edge
start with from_node=1
connect by nocycle prior to_node = from_node;
Ok, I would expect the following data to show up (which they do)
start_node | to_node | path_length
--------------------------------------
1 | 4 | 1
1 | 9 | 2
1 | 14 | 3
1 | 2 | 1
But then I also get
start_node | to_node | path_length
--------------------------------------
1 | 1 | 4
1 | 2 | 5
Which clearly is a cycle as it runs over 1 again (1->4->9->14->1->2).
Has anyone experience with that - or can anyone tell me how to eliminate those last two lines in the result? Thanks.
|
|
|
|
|
|
| Re: Connect By Prior and nocycles ... [message #353033 is a reply to message #352863] |
Fri, 10 October 2008 12:24  |
chlamy
Messages: 2
Registered: October 2008
|
Junior Member |
|
|
Hi Michel,
thanks for the answer. Well, I figured out that I could get rid of the 1 that causes the cycle by simply using the following statement:
SELECT 1 as start_node, to_node AS end_node, level AS path_length
FROM edge
WHERE to_node <> 1
start with from_node=1
connect by nocycle prior to_node = from_node;
But your example as well as mine still leaves me with the additional 2 at path_length 5 (and much more, as my example is just a simple one).
So I would need a preprocessing step in which to eliminate all rows of the output that are
- behind a 1 in cycle
- and before the following 1 in cycle
- have a higher path_length than the tuple with the last 1 in cycle
But for that I have to assume that Oracle will always provide the result in the order of the traversal. It generally is, but I don't think Oracle would guarantee it  .
I think, programming a function myself is the better way to do it unfortunately.
Anyway, thanks for the help,
chlamy
[Updated on: Fri, 10 October 2008 12:27] Report message to a moderator |
|
|
|
| Re: Connect By Prior and nocycles ... [message #353070 is a reply to message #353033] |
Fri, 10 October 2008 21:25 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Starting from FROM_NODE = 1, you get the following paths:
Path 1: (1,4)->(4,9)->(9,14)->(14,1)->(1,2)
Path 2: (1,2)
That's six rows in total, no cycles.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
