Skip to Main Content

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!

Recursive relationship in oracle

2790388Jan 11 2016 — edited Jan 12 2016

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

This post has been answered by Solomon Yakobson on Jan 11 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Feb 9 2016
Added on Jan 11 2016
4 comments
480 views