CODEWITHSUNDEEP
sqloracle-databaseconnect-by
José Carlos Ribeiro
José Carlos Ribeiro

Reputation: 121

CONNECT BY allowing only one cycle

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?

Upvotes: 0

Views: 365

Answers (3)

user5683823
user5683823

Reputation:

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.)

Upvotes: 3

Julian
Julian

Reputation: 4085

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

Upvotes: 0

José Carlos Ribeiro
José Carlos Ribeiro

Reputation: 121

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.

Upvotes: 3

Related Questions