Considerations for Aggregation Functions and Edit Codes

QMF calculates the result of an aggregation function based on the actual values stored in the database table, not on the values resulting from the edit code for a column. To obtain the aggregation result using the values resulting from the edit code for a column, you must use an alternative method such as defining a new column, and then using a REXX function.

For example:

1. Create and save the following query, naming it Q1:

   SELECT 10.5 from Q.ORG

2. Issue the commandRUN Q1 (ROW 2. The report appears as follows:

     COL1
   ------
     10.5
     10.5

3. Issue the command SH F. COL.
4. Position the cursor under COL1, and press the Insert function key.
5. Type COLNEW under COLUMN HEADING, SUM under USAGE for both COL1 and COLNEW, and change the edit code for COLNEW to L as shown below:

   FORM.COLUMNS                                         MODIFIED
    
                          Total Width of Report Columns: 20
    NUM COLUMN HEADING              USAGE   INDENT WIDTH EDIT  SEQ
    --- --------------------------- ------- ------ ----- ----- ---
      1 COL1                        SUM     2      6     L1    1
      2 COLNEW                      SUM     2      10    L     1
        *** END ***
    
    

6. Position the cursor under COLNEW, and press the Specify function key.
7. Choose Definition, and then press Enter.
8. Type the following REXX expression, and then press Enter:

   format(&1,5,0)

9. Press F12 to cancel the Specify window.
10. Press the Report function key to display the following report:

      COL1      COLNEW
    ------  ----------
      10.5          11
      10.5          11
    ======  ==========
      21.0          22

Note that COLNEW has rounded values for each row and that the sum is the sum of the rounded values.