To Edit the WHEN/DO template:
The report requires an average item price for each customer since a higher average indicates which customers purchase the more expensive items in our inventory. Specifying CUST-ID as the control break object is part of getting the average calculated for each customer. In this case, CUST-ID happens to be the only control break specified in the SORT statement and it is also the column to use in the WHEN statement. Using the optional word BREAKS clarifies that this action takes place after a control break.
For the DO portion, enter 'AVERAGE ITEM PRICE' as the legend so the report will have a meaningful identification for the result from the function. After the legend, enter AVG as the function and UNIT-PRICE to identify the column tells CA Dataquery which column to average.
Completed Example
Query entries are now complete. It appears as shown in the following panels. (On some terminals, you may have to scroll forward and backwards to view the entire query.)
Screen 1 of Query
=> --------------------------------------------------------------------------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....+. 01 FIND ALL CAI-DETAIL-TBL ROWS 02 WITH SHIP-QTY GT 0 03 SET SHIP-PRICE(7.2) = UNIT-PRICE * SHIP-QTY 04 SORT BY (CUST-ID) 05 PRINT FROM TITLE1 'FILLED CUSTOMER ORDERS' 06 CUST-ID 07 ITM-ID 08 SHIP-QTY 09 UNIT-PRICE 10 (SHIP-PRICE) PICTURE 'Z,ZZZ,ZZ9.99-' ------------------------------------------------------------------------------- <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
Screen 2 of Query
=> --------------------------------------------------------------------------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....+. 11 WHEN CUST-ID BREAKS 12 DO 'AVERAGE ITEM PRICE' AVG UNIT-PRICE .. =========================== 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
Other Things You Could Do
You could insert lines on the panel and type in your WHEN/DO statements. You could use the DISPLAY TABLES and DISPLAY COLUMNS options to insert table and column names into these statements. Since this query searches only one table, it is not necessary to use the optional table-name identifier.
When you select the WHEN/DO format from the query template, you could specify 2 or a higher number if you want multiple WHEN/DO statements in your query. Also, if you want multiple DO statements after your WHEN, you could use the EDITOR's C (COPY) line command to copy the DO format within your query or do a block copy of both WHEN and DO statements.
Using the BREAKS keyword clarifies when the function is to be performed, that is, after each control break in the report. You could use the FINISHED option to indicate that you want the average calculated only at the end of the report. However, for the sample query, this would not have given the results wanted, which was an average for individual customers.
If your query contains multiple control break columns in the SORT statement, you could have multiple WHEN/DO statements with different functions to be performed at each control break.
The PAGE-BREAK function may be used to put the results of column functions like AVG on a separate page. This can be done by putting the first and last DO statement in a WHEN clause as a PAGE-BREAK. The result is printing all other functions on a separate page.
Note: For information about PAGE-BREAK, see the CA Dataquery Reference Guide.
This query uses the AVG function. Depending on the results you want in your report, you can select any of the valid mathematical functions. You could have multiple DO statements that calculate the SUM or find the MAX or MIN of specific numeric columns.
|
Copyright © 2014 CA.
All rights reserved.
|
|