×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Sorting within a program

Sorting within a program

Sorting within a program

(OP)
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?  

RE: Sorting within a program

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

RE: Sorting within a program

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.

RE: Sorting within a program

(OP)
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

RE: Sorting within a program

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

RE: Sorting within a program

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.

RE: Sorting within a program

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.

RE: Sorting within a program

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

RE: Sorting within a program

See the KEYFLD paramater on OPNQRYF.

Solum potestis prohibere ignes silvarum.

RE: Sorting within a program

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.

RE: Sorting within a program

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.

RE: Sorting within a program

(OP)
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?
    

RE: Sorting within a program

(OP)
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?

RE: Sorting within a program

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.

RE: Sorting within a program

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) shadeshappy  .

RE: Sorting within a program

(OP)
Mercury2

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


Thanks for all the suggestions!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close