Re: SQL statement to read previous records

On 5/18/2016 4:40 PM, Rob Berendt wrote:

Multiformat files on the S/34 we're internally defined. Your RPG broke
them down. For example you might have

H01 - ORDER# - CUSTOMER# - SHIPTO - etc
D01 - ITEM# - ITEMQTY - PRICE - EXTENDED - etc
D01 - ITEM# - ITEMQTY - PRICE - EXTENDED - etc
D01 - ITEM# - ITEMQTY - PRICE - EXTENDED - etc
D01 - ITEM# - ITEMQTY - PRICE - EXTENDED - etc
H01 - ORDER# - CUSTOMER# - SHIPTO - etc
D01 - ITEM# - ITEMQTY - PRICE - EXTENDED - etc
D01 - ITEM# - ITEMQTY - PRICE - EXTENDED - etc
H01 - ORDER# - CUSTOMER# - SHIPTO - etc
D01 - ITEM# - ITEMQTY - PRICE - EXTENDED - etc
H01 - ORDER# - CUSTOMER# - SHIPTO - etc
D01 - ITEM# - ITEMQTY - PRICE - EXTENDED - etc
D01 - ITEM# - ITEMQTY - PRICE - EXTENDED - etc
D01 - ITEM# - ITEMQTY - PRICE - EXTENDED - etc
...

This was actually quite common back in the day.
And with RLA you just knew that the first four D01's were associated with
the first H01,
The next two D01's were associated with the second H01,
and so on.
So if you're going to do this with SQL you have to be aware of this. There
will be no trailing H01.

Chuck mentioned it, and it might do to mention it again with this very
nice example before us. I can (and do) write every one of my stored
procedures using RPGLE. I can read a stack of records, put them into an
array and return that array as a result set back to SQL.

The point being that if you already have an RPG program that processes
these files, you can put this logic into a stored procedure which ought
to be callable by an external app. via an ODBC connexion. No SQL is
needed in the RPG even though it is an SQL 'client' which is consuming
the results.

Our .NET team use these things all the time.