Next, if the request specifies a sort operation, the adapter must determine how to sort the data. The adapter transfers the sort operation to the RDBMS only when:

• It can generate a single SQL statement.
• Adapter-managed join optimization is not in effect.

For a discussion of these conditions, see Optimizing Joins.

Under these conditions, the adapter invokes one of the following types of RDBMS-managed sorting:

1. If the request does not use the direct operators FST. and LST., the adapter translates sort phrases (BY or ACROSS) into SQL ORDER BY clauses, thus passing responsibility for the primary sorting of data to the RDBMS. Note that BY TOTAL phrases are not optimized.
2. When the adapter determines that it will retrieve at most one segment instance per sort break, it translates FOCUS FST. and LST. operators to SQL MIN and MAX operators and translates sort phrases (BY and ACROSS) into SQL ORDER BY clauses. The adapter applies this technique in requests that explicitly specify the FST. or LST. operators and in requests that implicitly use them by referencing a non-numeric field in a report heading or footing.

   FOCUS FST. and LST. operators retrieve the first or last segment instance per sort break. By definition, if a FOCUS request includes FST. or LST. operators on multiple fields from one segment, each field value displayed on the resulting report must come from the same instance of that segment. SQL MIN and MAX operators do not dictate that the resulting fields all come from the same segment instance.

   Therefore, before translating the FST. and LST. operators in a FOCUS request to SQL MIN and MAX operators, the adapter must ascertain that it will retrieve at most one segment instance per sort break. It makes this determination by analyzing the sort fields in the request, the primary key of each segment in the join structure, and any join fields that are components of the primary keys.

   One segment instance is returned per sort break in any of the following situations:

   • The sort field includes a segment's entire primary key.
   • The sort field is a partial key joined to another sort field consisting of the remaining primary key component.
   • The request includes only one FST. or LST. operator on a segment.
3. If the report request contains FST. or LST. operators on a segment that may return multiple instances, the adapter directs the RDBMS to sort the data by primary key in the sequence specified by the Access File KEYORDER attribute. From this, the adapter retrieves column values for the logically first (FST.) or last (LST.) key values of the returned data. After the RDBMS sort, FOCUS re-sorts the data according to the sort phrases.

   Note: LST. processing is invoked for SUM or WRITE operations involving alphanumeric or date fields and for alphanumeric or date fields in a report heading or footing.

From a performance standpoint, consider using the TABLEF command in conjunction with RDBMS-managed sorting to free FOCUS from having to verify the sort order. Refer to Advanced Reporting Techniques, for the TABLEF command.