Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Connect_By_Iscyle Inconsistent?

Frank KulashDec 15 2016 — edited Dec 16 2016

Hi,

I don't understand exactly how CONNECT_BY_ISCYCLE and CONNECT BY NOCYCLE work.

When I run this query:

WITH    data    AS
(          SELECT  'Rock' AS parent, 'Scissors' AS child  FROM dual
UNION ALL  SELECT  'Paper',          'Rock'               FROM dual
UNION ALL  SELECT  'Scissors',       'Paper'              FROM dual
UNION ALL  SELECT  'Dynamite',       'Rock'               FROM dual
)
SELECT  SYS_CONNECT_BY_PATH (parent, '/')  AS path
,       CONNECT_BY_ISCYCLE                 AS iscycle
FROM    data
START WITH          parent  IN ('Rock', 'Dynamite')
CONNECT BY NOCYCLE  parent  = PRIOR child
;

I get these results:

PATH                                ISCYCLE
----------------------------------- -------
/Dynamite                                 0
/Dynamite/Rock                            0
/Dynamite/Rock/Scissors                   1
/Rock                                     0
/Rock/Scissors                            0
/Rock/Scissors/Paper                      1

The rows starting with 'Rock' are exactly what I expect, but for the rows starting from 'Dynamite', I expected these results:

PATH                                ISCYCLE
----------------------------------- -------
/Dynamite                                 0
/Dynamite/Rock                            0
/Dynamite/Rock/Scissors                   0
/Dynamite/Rock/Scissors/Paper             1

I don't see why a cycle is detected at 'Scissors' when I start from 'Dynamite', but not when I start from 'Rock'.

The SQL Language manual ( https://docs.oracle.com/database/121/SQLRF/pseudocolumns001.htm#SQLRF50939  ) says "The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0."  In the case of "/Dynamite/Rock/Scissors ", the only child of 'Scissors' is 'Paper', but the only ancestors (so far) are 'Dynamite' and 'Rock'.

Why do I get all the rows in the loop when I start from within the loop, but not when I start from outside the loop?

Is there a work-around, so I can get the '/Dynamite/Rock/Scissors/Paper' row included in the output?

I'm using Oracle 12.1.0.2.0, and I get the same results in 10.2.0.1.0.

This problem came up in another thread: Hierarchy query help

This post has been answered by James Su on Dec 15 2016
Jump to Answer

Comments

kdario

Hmm, it seems that this forum has some issues(or 'features')

Post above is reply to this thread: Image field cannot refresh when navigate records in jsf page so I don't know how this became totally new thread

Dario

BAO.SZ

Dario,

As you said, this topic is off the original thread (Image field cannot refresh when navigate records in jsf page), but is valuable to me. so I branch it as a new one.

I have two furthur question2:

1.Your suggestion to refresh page data seems a bit complicated, does it the best practice for the job?

--It seems, we also need to use Active Data technology?

2.Can have more evidence or reference docs on this sayings:

"normal way" to refresh page is usually very bad for applications based on JSF/ADF

Thanks.

BAO.SZ

I use the Actions-->branch feature.

But I cannot edit the first branched post, and you become the author of this thread.

Timo Hahn

BAZ, now things get messi

Your intention was good, however branching away the answer isn't something you, we or anybody should do. Now @"kdario" is in the drivers seat for this thread, not you. It's a flaw in the software that you can branch away part of the thread, not your fault.

Anyway I suggest that you open a new thread with your follow up question referring to the original thread. Abandon this one, please. We are happy to answer to the new thread.

Timo

BAO.SZ

Timo and kdario,

I'd glad to open a new thread for the topic. and thank you all very much for your patiences and kindness.

I think the 'Action-->.branch' is an useful feature when some improvement been made, that is:

When make the branching, the operator (which should be the author of the original thread) should be still the 'driver of the new thread',

and can edit the title and post content for the new thread.(but now cannot).

The benefit of above feature is :

1. the new thread usaually will have some relation with the original one, so keep the relation between them will make the contents/threads in this forum more relevent.

A relevant connected network of information will be more useful than a vast volume of unrelated segements of posts.

2.little inputing will be needed by branch if the author can edit the original branch out post.


Timo Hahn

I agree with you, however this is not the right place to discuss this. There is already an enhancement request (idea as it called in the new forum wording) for this at where you can add your comment and vote for the request to get implemented.

Timo

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 13 2017
Added on Dec 15 2016
16 comments
1,730 views