[SOLVED] Is there any to add one loop row in connect by oracle with nocycle?

Is there any to add one loop row in connect by oracle with nocycle?

Just like Oracle continues to follow a path beyond a cyclical loop when the cycle occurs at the top node (root node connected right back to root node), is there any way to do the same with in between cycle. Like if i have some data like below

create table t1 ( c1 varchar2(2), c2 varchar2(2));
insert into t1 values ('A', 'B');
insert into t1 values ('B', 'C');
insert into t1 values ('C', '**A**');

and execute below query:

select * from (
select distinct 
  connect_by_root c1 as c3,
  c1,
  c2
from t1 
connect by nocycle c1 = prior c2
) where c3='A';

It will give me this results

c3  c1  c2
A   A   B
A   B   C
**A**   **C**   **A**

It gives me the root looped valued. But if i have data like below.

create table t2 ( c1 varchar2(2), c2 varchar2(2));
insert into t2 values ('A', 'B');
insert into t2 values ('B', 'C');
insert into t2 values ('C', '**B**');


select * from (
select distinct 
  connect_by_root c1 as c3,
  c1,
  c2
from t2 
connect by nocycle c1 = prior c2
) where c3='A';

this gives me

c3  c1  c2
A   A   B
A   B   C

But i need third row also that is A C B. So wondering if this could be done?

Solution

You can use a recursive sub-query factoring clause:

WITH rsqfc ( c3, c1, c2 ) AS (
  SELECT c1, c1, c2 
  FROM   t2
  WHERE  c1 = 'A'
UNION ALL
  SELECT r.c3, t.c1, t.c2
  FROM   t2 t
         INNER JOIN rsqfc r
         ON ( t.c1 = r.c2 )
)
CYCLE c1, c2 SET is_cycle TO 1 DEFAULT 0
SELECT c3, c1, c2
FROM   rsqfc
WHERE  is_cycle = 0;

Which, for your sample data:

create table t2 ( c1, c2 ) AS
SELECT 'A', 'B' FROM DUAL UNION ALL
SELECT 'B', 'C' FROM DUAL UNION ALL
SELECT 'C', 'B' FROM DUAL;

Outputs:

C3 | C1 | C2
:- | :- | :-
A  | A  | B 
A  | B  | C 
A  | C  | B

db<>fiddle here