As you all know scott.emp
table of Oracle doesn't have any cycle in hierarchy.
But I changed emp
table a little (UPDATE emp SET mgr=7654 WHERE empno=7698)
where I changed Martin's manager as Blake and Blake's manager as Martin and fired following query.
SELECT
empno,
ename,
mgr,
level,
connect_by_iscycle
FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE prior empno=mgr
ORDER SIBLINGS BY mgr
But it doesn't give Martin and Blake related entries and doesn't display 1 at expected field. Please answer the reason if you can figure out.
Neither Martin nor Blake is ever reached in the hierarchy. You've made them mutually related, but no longer in any chain that you are selecting, because of the start condition.
The connection criteria is that prior emp = mgr
so based on that they are related, but neither is now linked to anyone else above them. They form their own little island, and that island doesn't match the mgr IS NULL
start condition. Or if you prefer, if you start from any record where mgr
is null there is no path down to either of these records.
If you removed the start condition you would see a lot more data you don't want, but that would include Martin and Blake and would show the cycle flag for them.