Purpose
The SORT statement allows you to organize the rows selected with the FIND statement. You can include a SORT statement in your query to list the selected rows in a specific order based on the values contained in selected columns.
Operation
With the SORT statement, you can order found set rows in ascending or descending order according to the contents of one or more columns. In addition, you can specify multiple SORT control-columns to allow sequencing of a group within a group; for example, you can sequence a group of customers by locale and then alphabetically by name within that locale.
You can designate control break columns in the SORT statement to cause columns designated for accumulation in the PRINT statement to be subtotaled whenever a value in a control break column changes. You can only specify accumulation of numeric columns in a PRINT statement. (See Designating Control Breaks for more information on accumulation of columns. See Totals Syntax Diagram for more information on printing totals.)
The total length of the sort key cannot exceed 100 characters. The sort key length is equal to the sum of:
Syntax Diagram
The following syntax diagram shows the keywords in a SORT statement.
►►─ SORT ─┬──────┬─┬─────────────────────────────┬─┬─────┬────────────────────► └─ BY ─┘ └─ table-name ─┬────────────┬─┘ └─ ( ─┘ └─ (status) ─┘ ►─┬─ key-name ────┬─┬─────┬─┬────────────┬───────────────────────────────────►◄ ├─ column-name ─┤ └─ ) ─┘ └─┬─ UP ───┬─┘ └─ result ──────┘ └─ DOWN ─┘
Repeat the parameters once for each SORT key or column name.
Explanation
The following explains the keywords in the SORT statement.
Used for readability only.
Specifies the Datadictionary defined table name with which the specified control-column is associated. When the column to be sorted is a temporary result from a SET statement, a table name should not be used since the result is not associated with any table.
If the control-column to be sorted belongs to the table most recently named in the query, repeating the table name is not necessary. However, if the control-column belongs to another table specified in the query, you must qualify the control-column with the corresponding table name.
(Optional) If authorized, use to specify a Datadictionary definition of a table that is being tested by programmers responsible for its maintenance. If the FIND or COUNT statement specifies a TEST status for a table, all subsequent statements in the same query reference the same status and version of the table, unless a different status is specified. The following chart describes each status you can specify.
Note: The status must be enclosed in parentheses, and there must be no spaces between the table name and the left parenthesis.
Finds the named table in the first test version that meets the specifications.
(Where nnn refers to the version number of the test status you want.) Finds the named table in the specified test version.
PRODuction is the default status if not specified.
Represents the name of the key which CA Dataquery uses to sort the found set. When this is a key, it must be contained within one of the tables specified in the FIND statement of the query. If a sort column is named UP, ASC, ASCENDING, DOWN, DESC or DESCENDING, use an alias for the column in the SORT statement. (An alias, or nickname, is assigned in Datadictionary to give an additional name to a column, key, or table.) Parentheses enclosing a SORT key or column name designate a control break. See Designating Control Breaks for more information.
Represents the name of the column which CA Dataquery uses to sort the found set. If named UP, ASC, ASCENDING, DOWN, DESC or DESCENDING, use an alias for the column in the SORT statement. (An alias, or nickname, is assigned in Datadictionary to give an additional name to a column, key, or table.) Parentheses enclosing a SORT key or column name designate a control break. See Designating Control Breaks for more information.
Represents the name of the result of a previous SET statement which CA Dataquery uses to sort the found set.
Indicates to arrange the row collection in an ascending direction based on the value of the control-column. If you do not specify the direction, CA Dataquery defaults to UP. You also can use the word ASCENDING or ASC to represent this direction.
Indicates to arrange the row collection in a descending direction based on the value of the control-column. You also can use the word DESCENDING or DESC to indicate this direction.
Sample
The following query illustrates sorting in an ascending direction.
FIND 10 CAI-SLSHST-REC ROWS
WITH SHIP-QTY > 0
RELATED BY SLMN-ID TO CAI-ORDERS-REC
SET NET-COST (8.2) = CAI-SLSHST-REC SHIP-QTY * UNIT-PRICE
SET DISCOUNT (5.2) = NET-COST * DISC-PCT
SORT CAI-ORDERS-REC BY ORD-ID UP
PRINT FROM CAI-ORDERS-REC ORD-ID
FROM CAI-SLSHST-REC ITM-ID
SHIP-QTY
UNIT-PRICE
NET-COST
DISCOUNT
Output
The following screen is sample output generated from executing this query.
=> 01/02/2010 CA Dataquery PAGE 1 16:52:32 DETAIL ORD-ID ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT ------ ---------- -------- ---------- ------------- --------- 08811 O50006 0000203 00014.75 00002994.25 00010.00 08811 O60005 0000003 00004.33 00000012.99 00002.12 08811 C70006 0001293 00010.00 00001293.00 00015.34 09031 C10006 0000011 00006.00 00000066.00 00003.99 09722 A90011 0000302 00003.00 00000906.00 00012.00 12810 O50006 0000100 00014.75 00001400.00 00005.23 -------------------------------- LAST PAGE --------------------------------- <PF1> HELP <PF2> RETURN <PF3> TOTALS ONLY <PF4> DETAIL <PF5> NO TOTALS <PF6> STATS <PF7> BACKWARD <PF8> FORWARD <PF9> NOT USED <PF10> NOT USED <PF11> LEFT <PF12> RIGHT
Sample
The following query and output illustrates using SORT to order data in a descending direction.
FIND 10 CAI-SLSHST-REC ROWS
WITH SHIP-QTY > 0
RELATED BY SLMN-ID TO CAI-ORDERS-REC
SET NET-COST (8.2) = CAI-SLSHST-REC SHIP-QTY * UNIT-PRICE
SET DISCOUNT (5.2) = NET-COST * DISC-PCT
SORT CAI-ORDERS-REC BY ORD-ID DOWN
PRINT FROM CAI-ORDERS-REC ORD-ID
FROM CAI-SLSHST-REC ITM-ID
SHIP-QTY
UNIT-PRICE
NET-COST
DISCOUNT
Output
The following screen is sample output from executing the preceding query.
=> 01/02/2010 CA Dataquery PAGE 1 16:52:32 DETAIL ORD-ID ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT ------ ---------- -------- ---------- ------------- --------- 12810 O50006 0000100 00014.75 00001400.00 00005.23 09722 A90011 0000302 00003.00 00000906.00 00012.00 09031 C10006 0000011 00006.00 00000066.00 00003.99 08811 C70006 0001293 00010.00 00001293.00 00015.34 08811 O60005 0000003 00004.33 00000012.99 00002.12 08811 O50006 0000203 00014.75 00002994.25 00010.00 -------------------------------- LAST PAGE --------------------------------- <PF1> HELP <PF2> RETURN <PF3> TOTALS ONLY <PF4> DETAIL <PF5> NO TOTALS <PF6> STATS <PF7> BACKWARD <PF8> FORWARD <PF9> NOT USED <PF10> NOT USED <PF11> LEFT <PF12> RIGHT
|
Copyright © 2014 CA.
All rights reserved.
|
|