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

[CRPence <CRPbottle@xxxxxxxxx>]

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.