Recursive query in DB2 to get all items in the chain
10,750
Here is the answer to your question using recursive CTE query:
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 = distance + 1
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 = 7);
http://sqlfiddle.com/#!3/8394d/16
I have left distance
inside the query to make it easier to understand.
Author by
daniel
Updated on June 13, 2022Comments
-
daniel almost 2 years
I have to retrieve all clients linked via loans by giving only one as input. Example I have a table data as
TABLEA
LOAN_ID CLIENT_ID 1 7 1 8 2 7 4 8 4 9 4 10 5 9 5 11 13 2 14 3
If I have given only input as CLIENT_ID=7 then the query has to select all the columns from above table except last two column because client_id 7 has 1,2 LOAN_ID and in 1 the CLIENT_ID 8 has loan_id=4 and in this loan CLIENT_id 9 has again 5 as loan_id.
can we write a sql query for this without stored procedure in DB2 ?
-
daniel over 9 yearsThanks Bulat for your query. I never used CTE/Recursion before. I am trying using START BY and CONNECT in DB2. Anyway thanks for your query and i understood better now on using RCTE.
-
daniel over 9 yearsi tested the query, but it was going to infinite loop.
-
daniel over 9 yearsi added and distance<1 ,,but if i add more than 1 like distance <7. it was taking very long time. is this how we need to limit the infinite loop ?
-
daniel over 9 yearsCYCLE and SEARCH DEPTH FIRST/BREADTH FIRST are supported from DB2 for i5/OS Version 5 Release 4. they are not supported DB2 (9.7)nor DB2 (10) for z/OS
-
marfi over 3 yearsPrecisely, so any idea how to replace them in an z/OS env?