Hi,

We are using DB2 V7 (MVS) in our shop, which dont have the luxury of the 'WITH clause' - CTE support to accomplish recursive queries.

Here is my problem -
Table1
From-ID To-ID
A1 M1 - Member A1's id changes to M1
M1 D1 - M1 changes to D1
D1 A2 - D1 changes to A2
A2 A1 - A2 changes to A1
R1 R2 - R1 changes to R2
R3 R2 - R3 changes to R2

Above table structure, records a series of changes that a member ID undergoes.

My problem is, given the member ID A2, my query has to return all first 4 rows.

As I mentioned earlier, I understand that DB2 version 7 for MVS doesnt support the CTE construct to handle recursive queries - Version 8 and up does.

Pls suggest me a query to handle the above problem. Appreciate your support on this.

Regards

Either you do the recursion in your application, or - if you know the maximum recursion depth to be at a reasonable level - try several self-joins.