I have a table which stores the customer , related customer and count of related customers.
The related customer can have again further related customers it's like a recursive relationships.
I wanted to find out all the related customers ( till the last related customer ) for a customer.
I had tried something like below which was giving me an error loop in user data
select *
from (select connect_by_root(cus_id) cus_id, rel_cus_id
from RELATED_TABLE
start with rel_cus_id is not null
connect by prior cus_id = rel_cus_id)
where cus_id <> rel_cus_id;
Table stats :-
---------------
Present volume is 2,00,000 records .
Create table
--------------
CREATE TABLE RELATED_TABLE
( CUS_ID VARCHAR2(09) ,
REL_CUS_ID VARCHAR2(09) ,
COUNT_OF_REL_CUST NUMBER(12)) ;
Sample data:-
---------------
INSERT INTO RELATED_TABLE VALUES ('402758970','898196448',3);
INSERT INTO RELATED_TABLE VALUES ('402758970','855115206',3);
INSERT INTO RELATED_TABLE VALUES ('402758970','850353774',3);
INSERT INTO RELATED_TABLE VALUES ('898196448','691094946',3);
INSERT INTO RELATED_TABLE VALUES ('898196448','404636299',3);
INSERT INTO RELATED_TABLE VALUES ('898196448','402758970',3);
INSERT INTO RELATED_TABLE VALUES ('855115206','870397045',3);
INSERT INTO RELATED_TABLE VALUES ('855115206','855115206',3);
INSERT INTO RELATED_TABLE VALUES ('855115206','402758970',3);
CUS_ID REL_CUS_ID COUNT_OF_REL_CUST
402758970 898196448 3
402758970 855115206 3
402758970 850353774 3
898196448 691094946 3
898196448 404636299 3
898196448 402758970 3
855115206 870397045 3
855115206 855115206 3
855115206 402758970 3
OUTPUT:-
--------------------
402758970 898196448
402758970 855115206
402758970 850353774
402758970 691094946
402758970 404636299
402758970 870397045
402758970 855115206