DQHJ0
=> --------------------------------------------------------------------------DQHJ0 DATAQUERY: FUNCTION CONTROL BREAK SELECTION QUERY NAME: USER-SAMPLE ------------------------------------------------------------------------------- Select the control break level at which the AVERAGE column function for the SHIP-PRICE column in the CAI-DETAIL-TBL table is to be displayed. The appropriate level may be indicated by placing any character next to the desired names. ------------------------------------------------------------------------------- SEL|LEVEL| CONTROL BREAK COLUMN NAME | CONTROL BREAK COLUMN DESCRIPTION ------------------------------------------------------------------------------- | |CAI-DETAIL-TBL | |001 | CUST-ID | | |END OF REPORT | X |000 | | END OF REPORT | | | | | | | | | | | | - LAST PAGE ------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> NOT USED <PF4> CONTINUE <PF5> NOT USED <PF6> DISPLAY QUERY <PF7> BACKWARD <PF8> FORWARD
Purpose
The optional FUNCTION CONTROL BREAK SELECTION panel allows you to tell CA Dataquery when to print the results of any function you selected. You can print results at each control break (each time the value in a control break column changes) or you can accept the default and print the result at the end of the report.
Panel Operation
On this panel, you use the Tab key to move the cursor to the field under the SEL heading and place a character next to the option you want. If you want the results printed at a particular control break, you select the control break name that you chose previously. For example, you would choose the DEPARTMENT control break if you wanted an average for each department and you had previously designated the DEPARTMENT column as a control break on the SORT CRITERIA DEFINITION panel.
Completed Example
The following completed FUNCTION CONTROL BREAK SELECTION panel specifies that an average price for all items will print for each customer.
=> --------------------------------------------------------------------------DQHJ0 DATAQUERY: FUNCTION CONTROL BREAK SELECTION QUERY NAME: USER-SAMPLE ------------------------------------------------------------------------------- Select the control break level at which the AVERAGE column function for the SHIP-PRICE column in the TEMPORARY RESULT COLUMN table is to be displayed. The appropriate level may be indicated by placing any character next to the desired names. ------------------------------------------------------------------------------- SEL|LEVEL| CONTROL BREAK COLUMN NAME | CONTROL BREAK COLUMN DESCRIPTION ------------------------------------------------------------------------------- | |CAI-DETAIL-TBL | x | 001 | CUST-ID | | |END OF REPORT | | 000 | | END OF REPORT | | | | | | | | | | | | - LAST PAGE ------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> NOT USED <PF4> CONTINUE <PF5> NOT USED <PF6> DISPLAY QUERY <PF7> BACKWARD <PF8> FORWARD
This chart explains each entry/selection.
|
Field/Item |
Sample Entry |
Reason |
|---|---|---|
|
LEVEL |
CUST-ID |
To see the average price of items ordered for each customer. |
|
LEVEL |
END OF REPORT |
The average is not wanted at the end of the report, so the x next to this level is deleted. |
Options
You could select both the control break and End-of-Report, to get an average for each customer and an average for all customers.
The sample query is defined. Following is the completed query as it appears on the EDITOR.
First Screen
=> QUERY CREATION COMPLETED --------------------------------------------------------------------------DQHM0 DATAQUERY: GUIDED QUERY DISPLAY QUERY NAME: USER-SAMPLE ------------------------------------------------------------------------------- To execute this query online, press <PF3>. To submit this query for batch execution, press <PF5>. To exit GUIDED QUERY and return to the MAIN MENU, press <PF4>. To continue query creation using GUIDED QUERY, press <PF6>. ------------------------------------------------------------------------------- ================================ T O P ====================================== FIND ALL CAI-DETAIL-TBL WITH SHIP-QTY GT 0 SET SHIP-PRICE(13.2) = CAI-DETAIL-TBL SHIP-QTY * CAI-DETAIL-TBL UNIT-PRICE SORT CAI-DETAIL-TBL (CUST-ID) PRINT TITLE1 'FILLED CUSTOMER ORDERS' CAI-DETAIL-TBL CUST-ID CAI-DETAIL-TBL ITM-ID CAI-DETAIL-TBL SHIP-QTY PICTURE 'Z,ZZZ,ZZ9-' CAI-DETAIL-TBL UNIT-PRICE PICTURE 'ZZ,ZZ9.99-' ------------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> EXEC QUERY <PF4> MAIN MENU <PF5> SUBMIT QUERY <PF6> NEW QUERY <PF7> BACKWARD <PF8> FORWARD
Second Screen
=> QUERY CREATION COMPLETED --------------------------------------------------------------------------DQHM0 DATAQUERY: GUIDED QUERY DISPLAY QUERY NAME: USER-SAMPLE ------------------------------------------------------------------------------- To execute this query online, press <PF3>. To submit this query for batch execution, press <PF5>. To exit GUIDED QUERY and return to the MAIN MENU, press <PF4>. To continue query creation using GUIDED QUERY, press <PF6>. ------------------------------------------------------------------------------- (SHIP-PRICE) CAI-DETAIL-TBL UNIT-PRICE PICTURE 'ZZ,ZZ9.99-' CAI-DETAIL-TBL ORD-ID PICTURE 'ZZ,ZZ9' (SHIP-PRICE) PICTURE 'Z,ZZZ,ZZZ,ZZZ,ZZ9.99-' WHEN CAI-DETAIL-TBL CUST-ID DO 'AVERAGE PRICE PER ITEM' AVERAGE SHIP-PRICE PICTURE 'Z,ZZZ,ZZZ,ZZZ,ZZ9.99-' ============================== B O T T O M ================================== <PF1> HELP <PF2> RETURN <PF3> EXEC QUERY <PF4> MAIN MENU <PF5> SUBMIT QUERY <PF6> NEW QUERY <PF7> BACKWARD <PF8> FORWARD
Explanation
This step tells CA Dataquery to sort (SORT) the rows found by the customer ID and to take a control break every time the customer ID changes (parentheses around the column name).
The rest of the query tells CA Dataquery which title to put on the report (TITLE1), which columns to print (PRINT), how to format the numeric data (PICTURE) and when to print column function results (WHEN/DO).
|
Copyright © 2014 CA.
All rights reserved.
|
|