Previous Topic: Validate QueryNext Topic: Creating Dialogs

Using CREATE in DQL Mode › Creating the Sample Query › Summary

Summary

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

User Sample Query

 FIND ALL CAI-DETAIL-TBL ROWS


           WITH SHIP-QTY GT 0


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


 SORT BY (CUST-ID)


    PRINT TITLE1 'FILLED CUSTOMER ORDERS'


                 CUST-ID


                 ITM-ID


               SHIP-QTY


                 UNIT-PRICE


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


    WHEN CUST-ID


         DO 'AVERAGE ITEM PRICE' AVG UNIT-PRICE

CREATE Function Sample Objective Report

         04/14/14                    CA Dataquery 15.0              PAGE   1


        14:56:22                        FILLED CUSTOMER ORDERS       DETAIL


     


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


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


         01008  C10000         0000002       00029.50          59.00


                C10001         0000004       00021.00          84.00


                C10002         0000006       00014.00          84.00


                C10005         0000002       00066.75         133.50


           TOTAL CUST-ID   01008                                        


                                                              360.50    




            AVERAGE ITEM PRICE 00032.81

Keyword or Symbol

Result

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 7 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.

(CUST-ID)

Since CUST-ID is enclosed in parentheses, it is a control break, meaning a total will be calculated for each customer.

PRINT

Produces a column report which includes a title and data for the specified columns.

TITLE1

Specifies the title for the report.

(SHIP-PRICE)

Calculates the total cost for all items shipped, since SHIP-PRICE is enclosed in parentheses in this statement. The total is calculated for each customer ID since CUST-ID is designated as a control break in the SORT statement, by virtue of being enclosed with parentheses.

PICTURE

Specifies the PICTURE clause, that is, format, for the output in the report. This format is enclosed in apostrophes and uses Zs to indicate that leading zeros will not be printed.

WHEN-DO

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