Re: how to retrieve records and make sure they arrive in RRN sequence

On 13 Feb 2013 08:06, Matt Olson wrote:

<<SNIP>>

At any rate, here is an updated version that should work for you in
SQL and will also only give you the fields you are wanting (no RRN
field):

SELECT field1,field2,field3
FROM ( SELECT RRN(b) AS RRN
,b.*
FROM file1 AS b
UNION
SELECT RRN(b)+(SELECT max(RRN(a)) FROM file1 AS a) AS RRN
, b.*
FROM file2 AS b
) tbl
ORDER BY RRN asc

You will notice this will push the file2 data to the end because it
will start with the last RRN from file1. Then we encapsulate that in
another SQL statement so you can just get everything except the RRN
column.

The following [as alluded, but not shown, by Vern] seems to be much simpler for using a constant [numeric vs character, so SrtSeq is moot] versus the numeric expression:

SELECT field1,field2,field3
FROM ( SELECT dec(1, 1) as file_nbr /* first file */
, RRN(b) AS RRN
, b.*
FROM file1 AS b
UNION
SELECT dec(2, 1) as file_nbr /* second file */
, RRN(b) AS RRN
, b.*
FROM file2 AS b
) tbl
ORDER BY file_nbr asc, RRN asc

SQL is the right tool for the job here IMHO.

I am not sure the picture presented by the OP is complete. Thus I am not so sure. There was mention of ordering first by MEMO#, but that does not [seem to me anyhow, to] jive with responses from others nor even some responses from the OP.

If the data access should not be from a copy of the data, i.e. must be from a logical view to the data, then SQL is of no help because an ORDER BY can not be specified in a VIEW. A non-query interface used to access the data from a DDS LF that implements the above UNION without either the constant or the RRN seems much simpler. Though I am still not clear on how the MEMO# fits, or how the data will be accessed, or whether the consumer must access the /ordered/ data from a file that has the same format level identifier [as implied by the OP in some replies].

Everything you can do in RPG you can also do in SQL, I have not yet
found anything that breaks that theory.
Very much the opposite, I found I can do way more in SQL then I can
do in RPG.

SQL can only CAST between types. RPG can overlay to remap data to any supported type. Basically, the RPG does program-described well while the SQL has great difficulty.