Find all dependencies between customers with recursive query ORACLE 11G

Question

I'm trying to write a recursive CTE to give me all of the other customers a "primary customer" depends on, but I don't know where to start.

I have created a sample data set and table structure:

create table T_CUSTOMER_RELATIONSHIP (CUST_ID VARCHAR2(10), OTHER_CUST_ID VARCHAR2(10))

insert into T_CUSTOMER_RELATIONSHIP values ('CUST1', 'CUST1');
insert into T_CUSTOMER_RELATIONSHIP values ('CUST1', 'CUST2');
insert into T_CUSTOMER_RELATIONSHIP values ('CUST1', 'CUST3');
insert into T_CUSTOMER_RELATIONSHIP values ('CUST1', 'CUST4');
insert into T_CUSTOMER_RELATIONSHIP values ('CUST5', 'CUST5');
insert into T_CUSTOMER_RELATIONSHIP values ('CUST5', 'CUST4');
insert into T_CUSTOMER_RELATIONSHIP values ('CUST5', 'CUST6');
insert into T_CUSTOMER_RELATIONSHIP values ('CUST7', 'CUST7');
insert into T_CUSTOMER_RELATIONSHIP values ('CUST7', 'CUST6');
insert into T_CUSTOMER_RELATIONSHIP values ('CUST8', 'CUST8');
insert into T_CUSTOMER_RELATIONSHIP values ('CUST8', 'CUST9');
insert into T_CUSTOMER_RELATIONSHIP values ('CUST8', 'CUST10');

commit;

The "primary" customer records have the same value of CUST_ID and OTHER_CUST_ID (so... CUST_ID1, CUST_ID5, CUST_ID7, CUST_ID8 are "primary" customers)

I want to get a result set for (say) CUST_ID1 as follows:

CUST_ID   OTHER_CUST_ID
CUST1     CUST2
CUST1     CUST3
CUST1     CUST4
CUST1     CUST5
CUST1     CUST6
CUST1     CUST7

...which will allow me to set up an extract script that will pull all of the details for all of the customers that CUST1 is related to either directly or through a common relationship with another primary customer (and its related customers, hence the recursion)

Is this possible?

You can't write a recursive CTE in Oracle 11g. Oracle introduced them in version 12C. You can use `connect by`, however. — Gordon Linoff, Oct 18 '19 at 10:44

score 0 · Accepted Answer · answered Oct 18 '19 at 11:43

You can use a hierarchical query:

SELECT DISTINCT
       CONNECT_BY_ROOT( cust_id ) AS cust_id,
       other_cust_id
FROM   T_CUSTOMER_RELATIONSHIP
START WITH cust_id = 'CUST1'
CONNECT BY NOCYCLE
       (  PRIOR other_cust_id IN ( cust_id, other_cust_id )
       OR PRIOR cust_id       IN ( cust_id, other_cust_id ) )
ORDER BY cust_id, other_cust_id;

outputs:

CUST_ID | OTHER_CUST_ID
:------ | :------------
CUST1   | CUST1        
CUST1   | CUST2        
CUST1   | CUST3        
CUST1   | CUST4        
CUST1   | CUST5        
CUST1   | CUST6        
CUST1   | CUST7

db<>fiddle here

Find all dependencies between customers with recursive query ORACLE 11G

1 Answers1