Previous Topic: Step 14: Specify When to Print Function ResultsNext Topic: Using CREATE in DQL Mode

Using GUIDE in DQL Mode › Creating a Sample › Step 15: Finalize Query

Step 15: Finalize Query

Pressing <PF3> EXEC QUERY executes the query and produces the finished report on the screen.

Compare the query to the sample report to see what specific part of the query produced that part in the report. Use the following chart to see what each query statement does in producing the report.

Query Created with Guided Query

           FIND ALL CAI-DETAIL-TBL ROWS


                 WITH SHIP-QTY GT 0


           SET SHIP-PRICE (13.2) = UNIT-PRICE * SHIP-QTY


           SORT BY (CUST-ID)


          PRINT TITLE1 'FILLED CUSTOMER ORDERS'


             CUST-ID


             ITM-ID


             SHIP-QTY


               PICTURE 'Z,ZZZ.ZZ9-'


             UNIT-PRICE


               PICTURE 'ZZ,ZZ9.ZZ9-'


             (SHIP-PRICE) PICTURE 'Z,ZZZ,ZZ9.99-'


               WHEN CUST-ID BREAKS


                DO 'AVERAGE PRICE' AVG SHIP-PRICE


                   PICTURE    'Z,ZZZ,ZZZ,ZZZ,ZZ9.99-'

On Guided Query Report

         04/14/11                  DATAQUERY                     PAGE     1


         14:56:2            FILLED CUSTOMER ORDERS               DETAIL


                                                                 


           CUST-ID    ITM-ID      SHIP-QTY     UNIT-PRICE     SHIP-PRICE


           ---------  ----------  -----------  -------------  -------------


            01008       C10000         2          29.50            59.00


                        C10002         6          14.00            84.00


                        C10001         4          21.00            84.00


                        C10005         2          66.75           133.50


   


                 TOTAL ORD-ID    01008


                                                                  360.50


            AVERAGE PRICE 00032.81

Statement

What Query Statement Does

FIND, WITH

Searches all CAI-DETAIL-TBL rows and locates those where the value for SHIP-QTY is greater than zero.

SET

Creates a temporary result named SHIP-PRICE and multiplies UNIT-PRICE by SHIP-QTY to calculate the total cost of each item shipped to a customer. Also designates that the resulting calculations can have up to 13 digits to the left of the decimal point and 2 digits to the right.

SORT

Places the located rows in ascending order according to the values in CUST-ID, which identifies the company ordering the items. Since CUST-ID is enclosed in parentheses, it is a control break column, meaning that, if specified on the PRINT statement, totals (or other functions) may be processed for each group of rows having the same customer number.

PRINT

Produces a columnar report which includes a title and data for the specified columns. Specifies the PICTURE clause (format) for the output in the report with Zs to indicate that leading zeros will not be printed.

TITLE1

Specifies the title for the report.

SHIP-PRICE

Calculates the total cost for all items shipped to a customer since SHIP-PRICE is enclosed in parentheses in this statement. ORD-ID, which you designated as a control break column in the SORT statement, controls when this total is calculated. Specifies the PICTURE clause, that is, the format, for the output in the report. This format is enclosed in single quotes and uses Zs to indicate that leading zeros will not be printed.

WHEN/DO

Calculates an average for the SHIP-PRICE values each time processing of data for a specific CUST-ID is completed (control break). The result of this WHEN/DO statement appears after control break totals. You can specify a legend in the DO statement to identify the results, such as AVERAGE PRICE PER ITEM.

Changing the Query

If you spot a mistake in your finished query, you can fix it. Just use the RETURN PF key to go back into GUIDE, make the change, and redisplay every selection that follows the change. Or, you can use the EDIT * command to change it using the CA Dataquery EDITOR.