I'm using opnqryf for sorting of a file. My problem is that I want to be able to sort on FieldA if not zeros, else FieldB. I know you can map a field that can be sorted on, but I'm not sure if I can accomplish dealing with this condition directly through the opnqryf command. Any suggestions?
By using OPNQRYF and mapped fields, you may be able to pull it off. What I would suggest, is to create a work file in QTEMP that is a copy of the file you are using, but add one field. Then with a CPYF to load the QTEMP file and either SQL or a program load the "sort field" with the value you want, either field A or field B. Now run the OPNQRYF to sort the data as needed. If this is used for a report, you're done. But if the result of the OPNQRYF needs to update the same database file, just chain back to the record and update it. When building the work file, if you use the same field names, very little coding will need to be changed.
I use OPNQRYF a lot along with work files. ONQRYF is a great command, but I mostly use it for selection and resorting. I have found that if a put too much weight on it, programs down the line are much more difficult to debug. Work files give you a change to easily see the result of the OPNQRYF and are much easier to maintain down the road.
This was first published in April 2001