IBM SC41-5210-04 manual Selecting sort fields in Query for iSeries, 105

Chapter 8. Selecting sort fields in Query for iSeries

This chapter describes how you can select sort fields to control the arrangement of the data in your query output. To Query, a sort field is a field whose contents are used to sort the output records in a particular order. For example, if you wanted to use a customer master file to obtain a list of all your customers, you might want those customers listed in a particular order, such as alphabetically by last name, alphabetically by state, or both alphabetically by state and then by last name. In this case, you would need to specify one or more sort fields to ensure that your output in is the order you want it to be in.

Letting Query for iSeries determine the order of records for you

You can let Query retrieve the data directly from the database and include it in your query without any sorting. If the sequence of the data in your report is not important, you do not need to worry about selecting sort fields. When running a query that does not have a sort field specified, the order in which rows are retrieved is not guaranteed. If the order is important, you should define a sort field in the query. A thorough knowledge of database concepts, including file type and access paths, is needed if you want to predict the sequence of your output.

Note: When you select a logical file for use with Query or DB2 UDB for iSeries programs without specifying any sort fields, unpredictable results may occur. For example, you may not receive the logical view of the physical file.

If you did not type a 1 next to the Select sort fields option on the Define the Query display, the Select Sort Fields display is not shown while you work with your query definition, and the data is not sorted when you run the query.

If you typed a 1 next to the Select sort fields option on the Define the Query display, the Select Sort Fields display is shown during query definition. Press F12 (Cancel) to return to the previous display if you have changed your mind and do not want to select any sort fields. (Anything you typed on the display is ignored.)

Selecting the sort fields you want to use in Query for iSeries

On the Select Sort Fields display, as shown below, you select the sort fields that you want by entering a sort priority number in the Sort Prty column. You can select up to 32 sort fields from the list. (The list on this display shows only those fields that were selected on the Select and Sequence Fields display; if no fields were selected, the list shows all the fields.) The total length of all the sort fields cannot be greater than 10 000 characters (that is, if you add up the lengths of the sort fields, the total cannot exceed

10 000 characters).

Based on the number you type for each field you select as a sort field, Query establishes a sort priority. You can use any numbers from 0 to 999—use the lowest number for your highest priority sort field. If you use numbers like 10, 20, 30, and so on, you can easily add another sort field later.

You can also specify whether you want the sort field sorted in ascending order (from lowest to highest value) or descending order (from highest to lowest value) by typing an A (for ascending) or a D (for descending) in the A/D column. (If you leave the A/D column blank for a field, the sort for that field is done in ascending order.)

© Copyright IBM Corp. 2000, 2002 105