-1

I created a table in DB2:

CREATE TABLE mytable
(
  loan_id integer,
  client_id integer 
);

Inserted values into the table:

INSERT INTO mytable(
loan_id, client_id)
VALUES (1, '2');

Finally the table contains the following data:

loan_id     client_id
1           2
1           4
4           2
2           3

I want to run a SQL query which would enumerate (print) all the unique possible paths. An example output is given below:

1 -> 2
1 -> 4
4 -> 2
2 -> 3
1 -> 2 -> 3
4 -> 1 -> 2
4 -> 2 -> 3
4 -> 1 -> 2 -> 3
3 -> 2 -> 4 -> 1

I looked into the following answer and tried the following code, which gives error:

WITH links AS
( SELECT 
    loan_id, 
    client_id as c1, 
    client_id as c2, 0 as distance 
  FROM 
    mytable 
  -- recursion 
  UNION ALL
  SELECT 
     t.loan_id, 
     l.c1 as c1, 
     tt.client_id as c2, 
     distance+ as distance 
  FROM 
    links l INNER JOIN
    myTable t ON l.c2 = t.client_id
    AND l.loan_id != t.loan_id INNER JOIN
    myTable tt  ON t.loan_id = tt.loan_id 
     AND t.client_id != tt.client_id
 )
SELECT * FROM mytable t
WHERE EXISTS 
    (SELECT * FROM links 
     WHERE c2 = t.client_id and c1 = 3);

Thanks for help.

Community
  • 1
  • 1
katya
  • 1
  • 1

2 Answers2

0

You can search in function SYS_CONNECT_BY_PATH

example here

Esperento57
  • 16,521
  • 3
  • 39
  • 45
0

In SQL SERVER VERSION:

WITH allvalue as ( select loan_id id from mytable union select client_id id from mytable ), allvaluewithnbcombi as ( select f1.*, (select count(*) from allvalue f2) nbcombi from allvalue f1 ) , tmprecurse (id, pathcalcul, rang, nbcombi) as ( select id , cast('' as varchar(50)) as pathcalcul, 1 rang, nbcombi from allvaluewithnbcombi union all select f1.id , cast(case when pathcalcul='' then cast(f2.id as varchar(50)) else f2.pathcalcul + '->' + cast(f2.id as varchar(50)) end as varchar(50)) pathcalcul,
f2.rang + 1, f1.nbcombi from allvaluewithnbcombi f1, tmprecurse f2 where f1.id<>f2.id and f2.rang<=f1.nbcombi ) , resultat as ( select distinct pathcalcul from tmprecurse where pathcalcul<>'' and pathcalcul like '%->%' ) select * from resultat order by len(pathcalcul)

IN DB2 VERSION (not tested)

WITH allvalue as ( select loan_id id from mytable union select client_id id from mytable ), allvaluewithnbcombi as ( select f1.*, (select count(*) from allvalue f2) nbcombi from allvalue f1 ) , tmprecurse (id, pathcalcul, rang, nbcombi) as ( select id , cast('' as varchar(50)) as pathcalcul, 1 rang, nbcombi from allvaluewithnbcombi union all select f1.id , cast(case when pathcalcul='' then cast(f2.id as varchar(50)) else f2.pathcalcul concat '->' concat cast(f2.id as varchar(50)) end as varchar(50)) pathcalcul,
f2.rang + 1, f1.nbcombi from allvaluewithnbcombi f1, tmprecurse f2 where f1.id<>f2.id and f2.rang<=f1.nbcombi ) , resultat as ( select distinct pathcalcul from tmprecurse where pathcalcul<>'' and pathcalcul like '%->%' ) select * from resultat order by len(pathcalcul)

I hope it's correct for you

Esperento57
  • 16,521
  • 3
  • 39
  • 45