Sorting within a program

[Ju5]

I need to create a RPG report that is to be sorted by Div, Mkt, and Grp. The relationship between the three fields are one to many(Many Grp under one Mkt, many Mkt under one Div). The file I am using(File1) is sorted by Div and Mkt, doesn't contain Grp but has a field called Flag which is to determine which records are to be included in the report and is passed to File1 using OVRDBF from another file. The Grp field is found in another file(File2). I created a Physical file DDS(File3) with all the fields of File1 + the Grp field from File2 and populated it by making File3 the output of a query which joined File1 and File2 together but doesn't get the get the effects of the OVRDBF.

I am thinking of using File3 as the target of the OVRDBF instead of File1 so that the flags can be passed to a file that has all the fields I need but I'm not sure it will work so I think I might need to sort File1 instead.

Does anyone know how to perform a sort inside an RPG program?

 

[jmd0252]

I take it these are all physical files why not do a logical??

 

[flapeyre]

You can either create a joined logical file (which will join File1 and File2, replacing File3 in your program), and dispense with the overrides, or join File1 and File2 with OPNQRYF.

I don't like to sort within an RPG program itself. This means loading the file into an array and using SORTA (there area also user space API's, but that's overkill for what you are doing). Because arrays are size-limited, and files are essentially not, I'd look at the join logical or OPNQRYF options.

Join Logical Files in DDS

OPNQRYF Command (scroll down to Example 14: Using a Join Query).

Solum potestis prohibere ignes silvarum.

 

[Ju5]

I'm not very familiar with the syntax for OPNQRYF join, can you give an example? I tried having the OVRDBF target File3 instaed of File1 and I received a CPF5116

 

[Ju5]

How do you add a field from file2 to file1 in an OPNQRYF?
What does SRTSEQ do?

 

[flapeyre]

Okay. CPF5116 means your program wrote more records than the printer file can process.

Are doing an OVRDBF SHARE(*YES) before you do the OPNQRYF?


Solum potestis prohibere ignes silvarum.

 

[flapeyre]

Don't worry about SRTSEQ. In it you can specify a table to sort data in a different order (for example, to give equal weight to upper and lowercase). But that's probably not your issue.

Solum potestis prohibere ignes silvarum.

 

[Ju5]

There's a SHARE before the OPNQRYF. Is there no way to sort files?

 

[flapeyre]

See the KEYFLD paramater on OPNQRYF.

Solum potestis prohibere ignes silvarum.

 

[Mercury2]

Ju5,
OPNQRYF is really a pain to code. You'd be better off using sql instead and insert the resultset in a temp table ordered by div, mkt, grp. Use afterward the RPG cycle against this temp file to create your report.

 

[flapeyre]

I think OPNQRYF vs. SQL is an individual preference. Personally, I find SQL (especially within an RPG program) harder to code (I do not have an SQL background). And when you come right down to it, they use the same query engine, anyway.

Solum potestis prohibere ignes silvarum.

 

[Ju5]

will the OVRDBF still work if I fo use SQL? The data needs to be overriden before the RPG cycle. Can you show me how to do it in SQL?


I found a few examples of a OPNQRYF join but I'm not sure if it will be correct for what I need:

OVRDBF FILE(ABPF0006) TOFILE(ABPF0001) +
OVRSCOPE(*JOB) SHARE(*YES)

OPNQRYF FILE((UAJIBAV/ABPF0001) (UAJIBAV/ABPF0002)) +
FORMAT(ABPF0006) JFLD((1/EMEMNO +
2/SAEMNO)) MAPFLD((PFEMNO '1/EMEMNO') +
(PFEMNM '1/EMEMNM') (PFDESG '2/SADESG') +
(PFSARY '2/SASLRY'))

CALL PGM

In the example above, if I need the OVRDBF to target ABPF0001 instead of ABPF0003 would it still work? Should ABPF0006 contain fields from both ABPF0001 and ABPF0002? Can I use KEYFLD(DIV MKT Grp) if Div and Market are in ABPF0001 while Grp is in ABPF0002?

 

[Ju5]

To clarify my question regarding FORMAT:

Should ABPF0006 be a PF containing all fields from both 0001 and 0002 or could you just take some fields from 0001 and some from 0002? Example: I only need 10 fields out of 15 from 0001 and 1 field out of 15 from 0002. If I define 0006 with 11 fields would it work or do I need to define 0006 with 30 fields?

If my override looks like this:
OVRDBF file(ABPF0001) share(*yes)

How would it affect the OPNQRY join?

 

[flapeyre]

You can put whatever fields you want in ABPF0006.

Try using those three as your key fields. If it doesn't work, you can create the ABPF0006 file a s physical file (with the proper keys), and after the OPNQRYF, use CPYFRMQRYF to copy them into ABPF0006. Then do a CLOF, a DLTOVR, and call your program. You will need ABPF0006 defined anyway to compile the program.

Solum potestis prohibere ignes silvarum.

 

[Mercury2]

Ju5,
I think that you're on the right route to create a new physical temp file3 keyed by Div, Mkt, Grp from file1 and file2. Populate file3 with the 10 fields you need from file1 and the one Grp fld from file2 in a first program then use the RPG cycle in an other program to edit your report from file3.
I'm fairly sure that you can do it simple and don't need this complicated opnqryf or sql (not less complicated but less pain to code) .

 

[Ju5]

Mercury2

I ended up using the method you suggested and was able to finish the report in record time!


Thanks for all the suggestions!