Re: Merging Files -- MIDRANGE-L

If the data in each file is a known date range, such as current year, lastyear, year before, then you can process each file in its entirety throughthe same logic that then loops to process the next file's content.

Although RPG source code references particular file names, the CL thatcalls the program, can override what file gets used several ways

OVRDBF I think is one way
OPNQRYF is another

and variables can be used from some standard call, if this override isneeded across a bunch of related programs

In RPG you could define twice (like two LFs over same PF) with a RENAME andALIASes, then matching fields and the RPG control cycle.


SQL cannot handle exceptions to RDB such as multiple formats of same PF.

Also be careful about files with multiple members.
We have PF with more members than LF can have.

In SQL embedded in RPG, we can identify same file with library specificity,using a variable to do so, sequencing the combined data via what is calleda CURSOR. The RPG F specs do not enter into that picture. The data can besorted by specifying in the SQL what sequence you want to process it.

However, every time I have combined multiple files JOINed in an SQL cursor,or individual SELECT, it has been different files, where aliases have notbeen a big problem. In your case, you need to move the input to a datastructure, so you have the input, then move it through field names thathave filtered out the aliases.

I suspect rename supported in DDS, when creating a JOIN logical, I justhave not used that feature. Depending on how you want to access file, whenjoining multiple PF, the combined key has to be totally from contents ofone of the two joined files. So you can go with1. Simple LF Join, with careful rename of the fields (from the 2+ fileswith same file names in the PFs) to give each uniqueness

2. Merge in OPNQRYF whose index comes from contents of the 2+ files
3. RPG rename the aliases back to the original PF names

If you have to fix the old version of the file to add the field now therein the newer versions, you might want to check whether any programsexplicitly use the old file. Depending on how they access it, changing thefile might break those programs.


When restoring files, where physical in one library, and logicals in another.
(a) If you do SAVE 21 RESTORE 21 you safe

(b) If you restoring piecemeal, all the physicals need to be restoredbefore the logicals that apply to them. This means the NAMEs of thelibrary with the logicals needs to be alphabetically after name oflibraries with physicals, unless after mass restore, you restore replacelogicals, or rebuild them somehow(c) Rules for some packages, like BPCS, are backwards on this topic ... inthat the name of library for new logicals over old physicals isalphabetically before. I think this is deliberate, because part of theirincome comes from helping customers that got messed up. If the system wasnot setup for so many prat falls, then their income would be less.

We have separate libraries for each year's history.  In order to perform
inquiries across multiple years rather than multi-defining each PF in
the program (which, honestly, is what I have been doing), I would like
to read them as a single file.



These tables have the same identical layout (fields, format names, file
names); just in different libraries.  I tried using a multiple format LF
but, naturally, it blew off because the record format names are the same.



Is there any way to read these two physical files via a single file
statement, or am I just spinning my wheels in the sand here?


--
        * Jerry C. Adams
*IBM System i Programmer/Analyst
B&W Wholesale Distributors, Inc.* *
voice
        615.995.7024
fax
        615.995.1201
email
        jerry@xxxxxxxxxxxxxxx <mailto:jerry@xxxxxxxxxxxxxxx>


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.