Re: Sorting records in Physical and Logical files

Pete Helgren wrote:

<<SNIP>>

Posted as somewhat a redundant reply & information, but may provide more clarification or just fodder for more questions.

So, am I correct in assuming that if the keys were in the order in the DDS I had defined, when an *RPG* program read them, it would be in the order that the keys were defined in (as long as K was in the F spec)?

There are /direct/, /keyed/, and /sequential/ access methods. The RPG HLL program can use any of those. A query can use any of those methods, even concurrently in combination. When an RPG program defines the file with the K, the retrieval is by the keyed order defined by the file in the F-Spec [as defined at compile-time, with whatever assorted impacts for the keyed file or ODP accessed at run-time].

Is that also true for logicals?

Same deal as with the physicals.

That is, the ONLY time the keys are determining the "order" of the records is when they are read through RPG?

Not just via RPG, but when retrieved via the /keyed access method/ by any interface to the database. Except by a database query ODP, e.g. generated by OPNQRYF, the keyed access method is not visibly manifest to a program outside of RLA methods. For non-RLA methods, the data is merely FETCHed, which is actually a sequential method, and retrieval by FETCH may be implemented as some type of sort versus any index.

Then I would expect that if I opened a physical file in Query OR
opened any associated logical in Query, the records would display in
arrival sequence regardless of how the DDS was defined in either the
logical or physical file?

The only expectation of the query should be to get the correct data according to selection; i.e. the set. Any expectation for ordering should be with respect to the ORDER BY [or /sort fields/ in a *QRYDFN object, or KEYFLD() for reading an ODP created by OPNQRYF,] that is specified on the query request; i.e. the set, ordered as requested. A database /query/ request is for a /set/ of rows. By itself, a _set_ has no order, but a set can be ordered. Without an explicit request to order the results, the /set/ is unordered, such that the /order/ of rows is unpredictable. Thus why earlier it was noted that not even the /arrival sequence/ can be assumed, regardless if testing seems to suggest that the order of returned rows appear consistent; that would be an assumption based upon a *false* inference.

If so, that is new information for me.
I thought DDS had broader implications than just in RPG programs.

The implication is broad, for whatever interface enables reference to the defined keys. Most notable is various other HLLs which also provide a Keyed Access method. Although a database query _can access_ the defined key, and *may* even reference & use the index for retrieval, the query has no requirement to do so. The query often _will not use_ the index, even if the ORDER BY or KEYFLD requests the same order as defined in an existing index; even for a query against an LF which defines the index with keys matching the ordering request. So although that implication is more broad than just RPG, that does not enable any possible inference for implementation on data retrieval, other than for Keyed Row Level Access; i.e. as provided by RPG and other HLLs.

Regards, Chuck