Using QMF

Creating a column using expressions

You can create a column in your report by defining an expression. An expression adds, subtracts, multiplies, or divides the values in two or more columns and places the results in a new column.

For example, you can create a total earnings column that is the sum of each employee's salary and commission.

To create a column:

1. Select the table, either by typing the name on the Tables panel or by selecting the table from the Table List.
2. Select the columns from the Columns panel by typing any character beside each one.
3. At the bottom of the Columns panel, select choice 1, Expression (A+B, etc.).
4. Press Enter. The Expression panel displays.

   Figure 27. Enter the expression you want to use.
   

   +--------------------------------------------------------------------------------+
   | PROMPTED QUERY                                       MODIFIED  LINE    1       |
   |                                 +-------------------------------------------+  |
   |   Tables:                       |                  Columns                  |  |
   |     Q.STAF +--------------------------------------------------------------+ |  |
   |            |                          Expression                          | |  |
   |   Columns: |                                                              | |  |
   |     NAME   | Type an expression.  You can use the following arithmetic    | |  |
   |     DEPT   | operators: add(+), subtract(-), multiply(*) and divide(/).   | |  |
   |     SALARY |                                                              | |  |
   |     COMM   | ( salary + comm                                            ) | |  |
   |            | (                                                          ) | |  |
   |   *** END  | (                                                          ) | |  |
   |            | (                                                          ) | |  |
   |            | (                                                          ) | |  |
   |            +--------------------------------------------------------------+ |  |
   |            | F1=Help  F4=List  F12=Cancel                                 | |  |
   |            +--------------------------------------------------------------+ |  |
   |                                 |   2. Summary Functions (SUM, etc.)...     |  |
   |                                 +-------------------------------------------+  |
   |                                 | F1=Help  F5=Describe  F7=Backward         |  |
   |                                 | F8=Forward  F12=Cancel                    |  |
   |                                 +-------------------------------------------+  |
   |                                                                                |
   |                                                                                |
   | OK, ENTER performed. Please proceed.                                           |
   +--------------------------------------------------------------------------------+

5. Enter your expression. For example, enter:

   SALARY + COMM
   

   You can create more complex expressions. For more information, press the Help function key on the Expressions panel. You can also press the List function key to see columns that you can use in your expression.

   The names of the tables and columns you selected are displayed in the echo area. The new column you created is also displayed under the heading Columns. The Specify panel displays again with choice 3, Row Conditions, selected.

   Figure 28. The new column SALARY+COMM appears in the echo area.
   

   +--------------------------------------------------------------------------------+
   | PROMPTED QUERY                                       MODIFIED  LINE    1       |
   |                                                    +------------------------+  |
   |   Tables:                                          |        Specify         |  |
   |     Q.STAFF                                        |                        |  |
   |                                                    | Select an item.        |  |
   |   Columns:                                         |                        |  |
   |     NAME                                           | 3 1. Tables...         |  |
   |     DEPT                                           |   2. Columns...        |  |
   |     JOB                                            |   3. Row Conditions... |  |
   |     SALARY                                         |   4. Sort...           |  |
   |     COMM                                           |   5. Duplicate Rows... |  |
   |     SALARY+COMM                                    +------------------------+  |
   |                                                    | F1=Help  F12=Cancel    |  |
   |   *** END ***                                      +------------------------+  |
   |                                                                                |
   +--------------------------------------------------------------------------------+

You can also create columns that summarize a group of rows. With QMF's summary functions, you can calculate:

• Sum
• Average
• Minimum
• Maximum
• Count rows

To specify a summary function:

1. On the Columns panel, select choice 2, Summary Functions (SUM, etc.).
2. Press Enter. The Summary Functions panel displays.
3. Select the functions you want to perform by typing a character in the space before the items.

   The Summary Function Items panel displays for any item you select except COUNT. The COUNT function does not need a column on which to act. In the echo area, COUNT is automatically assigned an asterisk, which indicates that it returns a count of the number of rows for each group.

4. Select the column on which you want the summary function to act.
5. Press Enter.

   QMF displays an error message if your expression is over 255 characters after the database processes it. If you see this message, return to the panel and specify your summary function so that the expression is less than or equal to 255 characters.

   The columns you selected are shown in the echo area, and the Specify panel displays again.