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

[CRPence <CRPbottle@xxxxxxxxx>]

On 13 Feb 2013 09:56, Stone, Joel wrote:
> Wow - that worked!!  Union ALL is the ticket.  So simple and elegant too.

  There is a huge difference between "that worked" and "that specific 
request to perform that SQL without an ORDER BY produced the desired 
effect that time, but at the whim of the optimizer for that invocation, 
so that same collation can not be expected to occur on every invocation 
of that same query."  Dependence on a false assumption is not a good 
proposition for software programming.

> <<SNIP>>
>
> UNION has an added feature of sorting the data - I didn't know that.

  The UNION is a synonym for UNION DISTINCT.  UNION DISTINCT does not 
"sort" the data, even though its DISTINCT processing may produce a set 
that might appear to have some apparent order.  Appearances can be 
deceiving.  There is no obligation of the database to return DISTINCT 
results in any particular order, when no ORDER BY is specified.

> UNION ALL leaves the data alone - like a CPYF. It simply concatenates
> a file onto the end of another, which is what I am looking for.

  The result of a UNION ALL is not defined as a concatenation of one 
file onto the end of another.  The UNION ALL returns an unordered set of 
rows, as a union of data from the tables named as a party to the union. 
The presumption of an arrival sequence concatenation is a fallacy, bound 
to be learned later, to have been based on a false assumption; though 
having been warned, that mistake can be avoided.

  Also FWiW the CPYF provides arrival sequence for copying all data 
from a file.mbr only when using FROMRCD(1) versus the default of 
FROMRCD(*START) which uses a keyed access path if one exists.