Identifying duplicate loops in Oracle

[TheFaQQer]

I'm not sure if there is an easy way to do this without a function (or even then...) but...

I have the following supervisor hierarchy:

Person 1 > Person 2 > Person 3 > Person 1

I can find the looping hierarchies no problem, but I get three records:
1 > 2 > 3
2 > 3 > 1
3 > 2 > 1

Can anyone think of a way to consolidate those three records to only one, since the loop data is the same (it's just the starting point that differs in each record)?

[doodab]

I'm not sure if there is an easy way to do this without a function (or even then...) but...

I have the following supervisor hierarchy:

Person 1 > Person 2 > Person 3 > Person 1

I can find the looping hierarchies no problem, but I get three records:
1 > 2 > 3
2 > 3 > 1
3 > 2 > 1

Can anyone think of a way to consolidate those three records to only one, since the loop data is the same (it's just the starting point that differs in each record)?

Assuming you're using start with connect by don't you need to refine your start with condition?

[TheFaQQer]

Assuming you're using start with connect by don't you need to refine your start with condition?

I'm doing

SELECT LEVEL                                        level1
,        connect_by_root     supervisor_number        top_supervisor
,        connect_by_iscycle                           loopback
,        connect_by_root     supervisor_id            top_supervisor_id
,        sys_connect_by_path(supervisor_number,'/')   path
FROM ( SELECT <rest of the stuff> ) a
WHERE CONNECT_BY_ISCYCLE > 0 
CONNECT BY NOCYCLE PRIOR a.person_id = a.supervisor_id 
ORDER SIBLINGS BY a.person_id 

The problem is that I get one record per person, when what I want is one record per loop.

There's some code on OTN here which has the same problem - one row per person, not per loop.

[doodab]

Hmm. I think you need to pull out the whole heirarchy in a subquery then flatten it with a group by to give you the min or max supervisor_id in each chain, and use that as your START WITH

Something like

SELECT LEVEL                                        level1 
-- ,        connect_by_root     supervisor_number        top_supervisor 
,        connect_by_iscycle                           loopback 
,        connect_by_root     supervisor_id            top_supervisor_id 
,        sys_connect_by_path(supervisor_id,'/')   path 
FROM doodab_test a 
WHERE CONNECT_BY_ISCYCLE > 0  
START WITH supervisor_id in (select min(connect_by_root supervisor_id)
FROM doodab_test a  
CONNECT BY NOCYCLE PRIOR a.person_id = a.supervisor_id
 group by person_id)
CONNECT BY NOCYCLE PRIOR a.person_id = a.supervisor_id  
ORDER SIBLINGS BY a.person_id     ;

[TheFaQQer]

Thanks - will have a play when back on the clock.

I can do it with a function - split the delimited group to find the lowest ID in the loop, then partition based on that, and then fetch records where row_number equals 1. Lots of sub queries and a function, which I'm not keen on.

Once I have a unique identifier for then loop, I can solve it. But I'm not thinking about it until Tuesday when I get paid again

[doodab]

Did you sort it?

[TheFaQQer]

Did you sort it?

Yes

In the end, I used a standard XML function which can strip the record and reorder it, and then only selecting the first record for the loop.

XMLTable('string-join(for $i in ora:tokenize($str, ",") order by xs:integer($i) return $i, ",")'
                     passing t.path as "str"
                     columns sorted_str varchar2(4000) path '.'

where t.path is the comma separated list from SYS_CONNECT_BY_PATH

Partition by the sorted string to get row_number() and use that.