CONNECT BY allowing only one cycle

Question

Let's say that I have the following table

  Row_ID | SourceId | TargetId
---------|----------|----------
    1    |    1     |    2
    2    |    2     |    3
    3    |    2     |    4
    4    |    4     |    5
    5    |    5     |    6
    6    |    6     |    5

I have to bring all of these rows in my query because they are all connected. However, when I do this:

SELECT Row_ID
FROM   MyTable
START WITH SourceId = 1
CONNECT BY NOCYCLE PRIOR TargetId = SourceId

It will not bring the row with Row_ID equals to 6.

I think it is because of the NOCYCLE keyword. But if I take it off, the query does not work since there is a cycle there.

I wanted to set a query that would bring me everything. Do you guys have any idea?

`RowID` - not good, it's a reserved word... make it `Row_ID`. — , Feb 23 '17 at 03:07

score 3 · Answer 1 · answered Feb 22 '17 at 23:17

3

I just found the solution.

SELECT myRowId
FROM   myTable
START WITH SourceId = 1
CONNECT BY NOCYCLE PRIOR TargetId = SourceId  or  TargetId = PRIOR SourceId

Sharing with you all. Thanks.

answered Feb 22 '17 at 23:17

José Carlos Ribeiro

121
2
9

I disagree. While this may work, it is not the "right" answer. (Of course - I just gave a different one, and I believe it is the right one!) – Feb 23 '17 at 00:21

score 3 · Accepted Answer · 2017-02-23T03:08:30.887

Cycles are detected based on the values in the columns included in the CONNECT BY clause, and specifically, ONLY those columns subject to the PRIOR operator.

In your example, even though it seems like this additional condition (below) should have no effect, it does. Just try it and you will see. Add

and PRIOR Row_ID IS NOT NULL

Of course, no Row_ID is ever null, so this doesn't change the logic; but now the value of Row_ID is added to the values considered in determining if a cycle exists, so you will be able to get all your rows.

(Note - I just edited my answer to change RowID to Row_ID to avoid conflict with an Oracle reserved word.)

Indeed, well said. Thank you! – José Carlos Ribeiro Feb 23 '17 at 12:42 — José Carlos Ribeiro, Feb 23 '17 at 12:42

score 0 · Answer 3 · answered Feb 22 '17 at 23:34

0

That will work. However you don't really need a CONNECT BY for what you want, not that is something wrong with it.

Here is an alternative version without using CONNECT BY

select *
  from test et 
 where exists(select 1
                from test it
               where et.targetId = it.sourceId OR it.targetId = et.sourceId)
 order by row_id;

It pretty much apply the same idea by working out which rows point to each other

answered Feb 22 '17 at 23:34

Julian

3,678
7
40
72

How do you do recursion, and the `start with` part? (The question only shows one related group of data; but if that was the entire table then `select myrowid from mytable` would be enough...) – Alex Poole Feb 22 '17 at 23:39
Maybe I misunderstood the question but the way I got it was that he wanted to list all records in his table that are related to each other; e.g. if it was a record there with a sourceId = 99 and targetId = 100 it would not have been selected. My fault if this was not what he wanted but this was my interpretation of ` I have to bring all of these rows in my query because they are all connected` – Julian Feb 22 '17 at 23:48

CONNECT BY allowing only one cycle

3 Answers3