Re: Keyed file is not appearing in sorted Order

On 07 Oct 2013 07:47, Nisha Ramesh wrote:

<<SNIP>>
I am creating a duplicate of FileA in QTEMP and then with OPNQRYF I
select only those balances of current Month. Then override the file
to <ed: the copy of the file in> QTEMP with Share(*Yes).

Apparently the temporary copy is because the WRITE activity that will occur, and thus the Open Data Path uses Open Options (OPTION) of *ALL to indicate that the temporary file via that ODP will support both READ and WRITE.?

Then I call an RPG program am reading FileA in a loop and using an
SQL am determining whether the date falls on Friday. If the SQL
returns value for Friday, then I will call a subroutine which will
increment the date by one and write a record (Using RPG WRITE
Opcode). Again the same for Sunday. At the end, using an SQL, am
calculating the average balance.

The mention of SQL is presumably, not germane; i.e. the calculations and decisions could have been made in any language.? The SQL can not utilize a shared Query ODP.

Instead of using both OPNQRYF to get the detail records and then the SQL to calculate averages, using GROUPING SETS of the SQL should enable generating both for input to the program.

But unfortunately the Sat and Sun records are not written in order,
it writes at the end. Please see the attachment for reference. I am
wondering if the file is keyed, then why the WRITE opcode is writing
at the end and not inserting the record in the order?

A WRITE has only a physical order. The logical order is defined by the ODP; in this scenario, apparently keyed per use of either KEYFLD(*FILE) or KEYFLD((date_column)).

The effect seems possible, to reflect the use of /stale/ vs /live/ data. The OPNQRYF provides an Allow copy of data (ALWCPYDTA) parameter which tells the Query feature to implement the query over the live data rather than a copy of the data; i.e. explicitly specify ALWCPYDTA(*NO) because [what I infer is] the intention of the query is being defeated by the use of the Query temporary copy of [your temporary copy of] the data.

I know the query appears is little big.. but am highly dependent on
your valuable inputs. Please help me if you are able to find where
am missing.

The /attachment/ did not make it to the NNTP interface. The http://code.midrange.com can be used to place some code, and then the URL provided by that tooling, from having pasted the code snippet, can be included in a message posted to the forum [via the email list or via NNTP]; thus avoiding the restriction on attachments.

Because that information is not available, I can only guess... But, I suspect that the SQL might be able to fairly simply accomplish all of the work in preparation for the /monthly/ program, even more efficiently than the current [apparent] design requiring the temporary copy of the file against which the output\write capability is required.