Using CREATE in DQL Mode › Creating the Sample Query › Step 4: Create Temporary Results › Edit SET Statement

Edit SET Statement

To edit the SET statement template, follow these steps:

1. Complete the SET statement by typing over result (13.2) with SHIP-PRICE (7.2) = UNIT-PRICE * SHIP-QTY.
2. Position the cursor in the first blank space on line 04 and press the ERASE key to remove the remainder of the SET format.

Before retrieving the SET format from the query template, the query has only three lines, with line 03 being blank. Upon returning to the CREATE MODE panel, the query contains four lines since the SET format is two lines long. The SET format uses line 03 for the first line of the format and inserts line 04 for the second line of the format. If you are adding statements to the bottom of a query with template, CA Dataquery inserts the required lines for you.

Since the mathematical expression is simple and short, it is easier to type it in on line 03 and delete the unnecessary part on line 04. The next step is to name the temporary result SHIP-PRICE and set the precision at (7.2). Using the two columns UNIT-PRICE and SHIP-QTY makes it possible to calculate the result for SHIP-PRICE. UNIT-PRICE contains the price of each item and SHIP-QTY tells how many of that item the customer has ordered. The operation to perform is multiplication to determine the total price for each item the customer is ordering.

Completed Example

 =>

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

Options

You can specify a number greater than 1 on the template if the query is to contain multiple SET calculations. You could also select only one SET format and use the EDITOR's C (COPY) line command to copy the format multiple times on the CREATE MODE panel. Further details on EDITOR line commands are available in the CA Dataquery Reference Guide.

If you do not know the name or data type of a column, you can use <PF3> DISPLAY COLUMNS to display the attributes of the columns and select the appropriate ones for your calculations.