Chapter 28: Sorting Data

Overview

More often than not, the records in a database file will not be in the sequence you need them for a particular application program. For example, you may have an Inventory Transaction History file that is defined as an arrival-sequence file. Records are added all the time, in the order the transactions are posted to Inventory.

If you want to print a report that lists transactions sorted by warehouse number and within each warehouse, sorted by item number, you clearly need to sort (rearrange the order of) the file. Once the file is sorted the way you need it, you would be able to print the report you want.

Later, someone else may need to print a different report based on the same file. The new report requires that the records be listed in reverse order of their transaction value, pick up only sales transactions, but exclude other types of transactions such as inventory adjustments and purchases. This points out another common requirement while sorting: specifying which records to include and, of course, which records to exclude.

This chapter is all about four choices you have for getting records into the sequence you need. You can use a key on your physical file, a logical file, the Open Query File (OPNQRYF) command, and the Format Data (FMTDTA) command. The method you choose will likely be predicated upon what you use the data for. These sorting methods are available under i5/OS without having to purchase additional program products. Another method, the use of SQL/400, is worthy of treatment in itself and is covered in Chapter 25.

As a general rule of thumb, physical keys and logical files are recommended for jobs that will be performed interactively or online. Batch or submitted jobs are generally better off using the OPNQRYF and FMTDTA commands, unless a primary or logical view for the desired path has already been established.