Hello wise ones,

I have an IMAGE/SQL dataset with patch supersede info similar to

  PATCH_ID  NEXT_ONE
  --------  --------
  MPEXY01   MPEZZ88
  NSTFD10   NSTFD33
  MPEZZ88   MPEXY99
  ...       ...

and want to retrieve the "successor chain" for a given patch ID,
for example "MPEXY01 -> MPEZZ88 -> MPEXY99" in above table snippet.

This is pretty straightforward using TurboIMAGE intrinsics, just
a sequence/loop of lookups with DBFIND & DBGET pairs. Works fine
and also fast (in Transact/iX, for example).

Now I am trying to retrieve the same result via SQL access (with
JDBC, to be more precise). While I can use a sequence -ahem- loop
of "SELECT next_one FROM my_table WHERE patch_id=xyz;" statements,
it is painfully slow due to the associated overhead of SQL parsing
and the client/server network connection.

I guess I would be better off by using some clever join or alike
that returns all my desired data with one request. Or maybe use
a store procedure to achive my goal. Unfortunately this is beyond
my current SQL knowledge at present.

Any tips or pointers to tips?

Thanks, Lars.