BY.EXP

BY.EXP, BY-EXP

Top  Previous  Next

 

The BY.EXP sort clause keyword applied to a multivalued field causes the query processor to explode the multivalued items to form separate single valued records and to sort these into ascending order prior to display or when building a select list.

 

 

Format

 

BY.EXP {NO.CASE} field {explosion.limiter}

 

where

 

fieldis the field name or evaluated expression to be used to determine the sort order.

 

explosion.limiteris a condition similar to that specified in a WHEN clause to limit the records and values included in the report.

 

 

The BY.EXP keyword causes records to be sorted into ascending order of the values stored in the specified field. The comparison is performed before conversion of the data to its display format. If the display format is left justified, a left justified sort is performed. Conversely, if the display format is right justified, a right justified sort is performed.

 

The optional NO.CASE keyword causes the sort to be performed in a case insensitive manner. Effectively, all comparisons are done using an uppercase version of the data being compared. Use of the QUERY.SORT.NO.CASE setting of the OPTION command implies the NO.CASE qualifier.

 

If more than one sort clause is present, sort criteria are applied in the order in which they are specified. If more than one exploded sort clause is present, they must all be on the same association.

 

 

Example

 

The command

 

LIST ORDERS PART.NO QTY LINE.TOTAL

 

might produce a display such as that below.

 

LIST ORDERS PART.NO QTY PRICE LINE.TOTAL

ORDER   PART   QTY   PRICE   TOTAL.

24842    648     7   10.00    70.00

        216     3    8.00    24.00

24851    107     2   12.50    25.00

24856    319     6    4.50    27.00

        372     1   18.75    18.75

3 records listed.

 

The command

 

LIST ORDERS PART.NO QTY LINE.TOTAL BY.EXP PART.NO

 

applied to the same data would produce the display below.

 

LIST ORDERS PART.NO QTY PRICE LINE.TOTAL

ORDER   PART   QTY   PRICE   TOTAL.

24851    107     2   12.50    25.00

24842    216     3    8.00    24.00

24856    319     6    4.50    27.00

24856    372     1   18.75    18.75

24842    648     7   10.00    70.00

3 records, 5 values listed.

 

Adding an explosion limiter to this query to show only part numbers greater than 300:

LIST ORDERS PART.NO QTY LINE.TOTAL BY.EXP PART.NO > 300

would produce the report below.

 

LIST ORDERS PART.NO QTY PRICE LINE.TOTAL

ORDER   PART   QTY   PRICE   TOTAL.

24856    319     6    4.50    27.00

24856    372     1   18.75    18.75

24842    648     7   10.00    70.00

2 records, 3 values listed.

 

 

See also:

BY, BY.DSND, BY.EXP.DSND, REPEATING