Ordering the result segment rows

If you want to guarantee that the rows in your result segment are ordered in a particular way, you must specify the order in the SELECT statement. Otherwise, IMS can return the rows in any order.

To retrieve rows in a specific order, use the ORDER BY clause. Using ORDER BY is the only way to guarantee that your rows are ordered as you want them. The following topics show you how to use the ORDER BY clause.

Specifying the sort key in the ORDER BY clause:

The order of the selected rows depends on the sort keys that you identify in the ORDER BY clause. A sort key is a field name of the segment. IMS orders the rows by the first sort key, followed by the second sort key, and so on.

You can list the rows in ascending or descending order. Null values appear last in an ascending sort and first in a descending sort.

Example: ORDER BY clause with a field name as the sort key: The following statement retrieves all hospital names, sorted in alphabetical order:

SELECT HOSPITAL.HOSPNAME
   FROM PCB01.HOSPITAL
   ORDER BY HOSPITAL.HOSPNAME ASC