Re: Recursive SQL on the i

Hi Joe,

The scenarios where recursive SQL is a good choice are the same as on any
other database.

Recursive SQL is not necessarily for complex result sets, but it does
support complexity (e.g. one recursive produced result set serves as input
to a second recursive query, etc.). I just built and ran a simple one with
14 lines of SQL code (de-normalize, delimit, and concatenate task).

Any query where the production of one row is dependent on data from a
previously produced row, may be a good candidate for recursive SQL.

Some uses / tasks:

- De-normalization
- Concatenation
- Linked lists
- Processing hierarchical data (e.g. org charts, bill of materials)
- Flight booking applications (one or more stops on way to destination)
- Truck delivery appointment applications (more than one delivery
appointment)
- Estimation
- Project planning (start time of one task is dependent on completion of
prior task)
- Building a list of dates within a date range
- Many others...

The common thread in those tasks is the production of one row is dependent
upon a previously produced row.

In RPG, such an application would have two (or more) rows resident in
memory at the same time (previously produced row and a subsequent one under
construction).

Recursive SQL is not hard, but does take some practice at first.

Mike

date: Thu, 29 Jun 2017 13:42:02 -0400
from: Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>
subject: Recursive SQL on the i

HI I was interested in how you might be using Recursive SQL on the i.

From what I have read on the IBM web site on this topic,. it seems most

used in very large enormous, complex, data sets.

My way has always been to break the task into smaller manageable parts.

Perhaps someone who really knows can explain this better.

Thank you,
Joe