In this step, you see how you can specify that data is to be sorted in a specific order and specify breaks to control totaling operations for your report.
Concepts
These are the major concepts related to this step:
SORT
Tells CA Dataquery that the data it retrieves (from the database or temporary results) is to be arranged in a specified order in the report. Your query can contain only one SORT statement.
Table-name
Tells CA Dataquery which table contains the data you want to sort. This is an optional entry if you are searching only one table. If you are searching two or more tables, you use this field to clarify which table contains the columns you are using in your SORT statement. You can use columns from multiple tables in the SORT statement. You can also use temporary results.
BY
Tells CA Dataquery that the columns following this word are to be used as sort data.
Column-name
You specify the name of the column containing the critical data for the report's ordering. The sequence in which you list these columns determines which is the primary sort column, which is the secondary, and so forth. When you specify sort columns, make certain the sequence of sorting requests is logical. For example, you could specify that a query is to sort the data by ZIP as the primary sort column then by STATE as the secondary. This is not a logical choice since multiple zip codes exist in a single state. A better sequence would be to specify STATE as the primary sort and ZIP as the secondary. All of your sort columns together cannot exceed 100 characters in length. If you specify the name of a column which is a repeating field, you must use subscripts to identify the occurrence. See Repeating Fields for more information.
Control break
You can designate control break columns by enclosing the column in parentheses in your SORT statement. Each time the value of the data in the designated control break changes, totals can be accumulated for numeric columns. (Step 6 designates which numeric columns are to be totaled.) Since numeric data can be different for each row, designating numeric columns for your control breaks may not give you the desired results for your report. Instead, you may want to select columns containing character data for your control breaks. For example, to get a total of all sales by sales representatives in a region, you could designate STATE as your control break. Each time CA Dataquery encounters the name of a different state in the selected data, a total could be produced for the amount of sales in that state. You may designate up to 10 control break columns in a query.
Order
Specifies whether the data for your report will be sorted in ascending order, A through Z for character or 0 through 9 for numeric, or in descending order, Z through A for character and 9 through 0 for numeric. The default is UP for ascending. If you want your report in descending order, specify DOWN.
Procedure
The last step created a temporary result named SHIP-PRICE column based on UNIT-PRICE and SHIP-QTY values. The query now has a FIND statement, a WITH clause, and a SET statement. Following is an example of how it looks now:
=> --------------------------------------------------------------------------DQD10 DATAQUERY: EDITOR CURRENT TABLE: CAI-DETAIL-TBL ------------------------------------------------------------------------------- NAME: USER-SAMPLE TYPE: QUERY STATUS: PRIVATE DESCRIPTION: TOTAL ORDERS PER CUSTOMER -- AVERAGE PRICE ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+. .. ================================ T O P ===================================== 01 FIND ALL CAI-DETAIL-TBL ROWS 02 WITH SHIP-QTY GT 0 03 SET SHIP-PRICE(7.2) = UNIT-PRICE * SHIP-QTY 04 .. =========================== B O T T O M ================================== ------------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> DISPLAY COLUMNS <PF4> DISPLAY KEYS <PF5> DISPLAY ALL <PF6> LIST TABLES <PF7> BACKWARD <PF8> FORWARD <PF9> TEMPLATE <PF10> VALIDATE <PF11> RIGHT/LEFT <PF12> PROCESS MODE
|
Copyright © 2014 CA.
All rights reserved.
|
|