SORT Statements (DQL)

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.
datacom151
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.
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:
  • Seventeen (17) for each temporary result named in the SORT statement
  • The length of each database table column
  • One (1) for each signed database table column
A SORT statement can use the following data types: NUM DEC, BIN, 1/2 BIN, FUL BIN, DBL CHAR, DBL MIXED, KANJI.
Topics discussed on this page include:
 
 
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.
The following explains the keywords in the SORT statement.
  •  
    BY
    Used for readability only.
  •  
     
    table-name
     
    Specifies the 
    Datacom 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.
  •  
     
    (status)
     
    (Optional)
     If authorized, use to specify a 
    Datacom 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.
     The status must be enclosed in parentheses, and there must be no spaces between the table name and the left parenthesis.
  •  
     
    key-name
     
    Represents the name of the key which 
    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 
    Datacom 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.
  •  
     
    column-name
     
    Represents the name of the column which 
    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 
    Datacom 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.
  •  
     
    result
     
    Represents the name of the result of a previous SET statement which 
    Dataquery
     uses to sort the found set.
  •  
    UP
    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, 
    Dataquery
     defaults to UP. You also can use the word ASCENDING or ASC to represent this direction.
  •  
    DOWN
    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
Sorting Groups Within Groups
To sort a group within a group, include additional sort control-columns. The data is sorted according to the order in which the sort columns are specified.
 
Sample
 
For example, the SORT statement in the following sample specifies ORD-ID and SHIP-ID as the two sort columns. 
Dataquery
 first sorts the ORD-ID data and then sorts by SHIP-ID within each order.
FIND 10 CAI-SLSHST-REC ROWS WITH SHIP-QTY > 100 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 AND SHIP-ID PRINT FROM CAI-ORDERS-REC ORD-ID SHIP-ID FROM CAI-SLSHST-REC ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT
 
Output
 
The following sample output is generated from executing the preceding query.
=> 01/02/2010 CA Dataquery PAGE 1 18:11:54 DETAIL ORD-ID SHIP-ID ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT ------ ------- ---------- -------- ---------- ------------- --------- 08811 00001 O50006 0000203 00014.75 00002994.25 00010.00 08811 00002 O70001 0000002 00010.00 00000020.00 00010.00 09012 00007 C00006 0000100 00004.00 00000400.00 00002.00 09013 00003 O50006 0000112 00100.00 00001112.00 00002.01 -------------------------------- 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
Designating Control Breaks
When a column in a SORT statement is enclosed in parentheses, it becomes a control break. Whenever the value contained in a control break column changes, a control break occurs.
 
Purpose
 
The primary use for control breaks is to specify when subtotals will be displayed for columns that have been designated for accumulation in the PRINT statement and to trigger processing of special functions defined in the WHEN/DO clause. (See Specifying Functions at Control Breaks for more information.) When the PRINT statement is executed, a subtotal for each accumulation column is presented whenever the value changes in the control break. After all data has been presented, a grand total appears as the last line of output.
You can also use control breaks to make reports easier reading. If the SUPPRESS DUPLICATE COLUMNS options is set to YES in your User Profile, the value contained in the control break is displayed only once on the screen. The value appears on the first line in the column designated for the control break. The column remains blank until the value in the control break changes.
You can designate up to ten control breaks in the SORT statement. Control breaks and ordinary columns can be freely intermixed.
 
Sample
 
The following query illustrates the use of control breaks. The control break column is ORD-ID, which is enclosed in parentheses in the SORT statement. Accumulation columns are not specified in this example.
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) AND SHIP-ID PRINT FROM CAI-ORDERS-REC ORD-ID SHIP-ID FROM CAI-SLSHST-REC ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT
 
Sample
 
The following screen illustrates the effect when the SUPPRESS DUPLICATE COLUMNS option is set to YES. The value in the control break is presented only one time and the report column remains blank until the value in the control break changes.
 
Results with Suppress Duplicate Columns Set to YES
 
=> 01/02/2010 CA Dataquery PAGE 1 16:59:12 DETAIL ORD-ID SHIP-ID ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT ------ ------- ---------- -------- ---------- ------------- --------- 08811 00001 O50006 0000203 00014.75 00002994.25 00010.00 00002 O70001 0000002 00010.00 00000020.00 00002.33 09012 00007 C00006 0000100 00004.00 00000400.00 00012.82 00008 900006 0000100 00006.00 00000600.00 00002.82 00007 070006 0000100 00010.00 00001000.00 00010.00 09013 00008 O50006 0000112 00100.00 00001112.00 00002.01 09014 00008 O50006 0000002 00100.00 00000200.00 00002.09 -------------------------------- 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 illustrates the use of control breaks to present subtotals for columns designated as accumulation columns in the PRINT statement. The accumulation column in this example is NET-COST and it is enclosed in parentheses in the PRINT statement.
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) AND SHIP-ID PRINT FROM CAI-ORDERS-REC ORD-ID SHIP-ID FROM CAI-SLSHST-REC ITM-ID SHIP-QTY UNIT-PRICE (NET-COST) DISCOUNT
 
Output
 
When the preceding query is executed, the screen displays a subtotal for NET-COST each time the value of ORD-ID changes. The following screen shows the results of the query.
=> 01/02/2010 CA Dataquery PAGE 1A 17:03:11 DETAIL ORD-ID SHIP-ID ITM-ID SHIP-QTY UNIT-PRICE NET-COST DISCOUNT ------ ------- ---------- -------- ---------- ------------- --------- 08811 00001 O50006 0000203 00014.75 00002994.25 08982.75 00002 O50006 0000010 00014.75 00000147.50 00002.75 TOTAL ORD-ID 08811 * 00003141.75 09312 00012 O50006 0000100 00014.75 00001475.00 04432.75 00015 O90001 0000010 00006.00 00000060.00 00012.75 TOTAL ORD-ID 09312 * 00001535.00 * GRAND TOTAL -------------------------------- 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
After the word TOTAL, the name of the control break (ORD-ID) appears, followed by the value in the column. An asterisk precedes the accumulated value for the subtotal.
The last line of output presented is a total for the accumulated columns. The total is designated by * GRAND TOTAL and an asterisk preceding the accumulated value.
SORT Limitations
Following are important considerations to observe when constructing SORT statements.
  • Ten control breaks in a SORT statement is the maximum allowed.
  • The total length of columns/keys used in a SORT statement cannot exceed 100 characters. (An extra character is added for each signed numeric column.)
  • SQL DATE, TIME and TIMESTAMP cannot be used with DQL SET, SORT and WITH.