Using QMF

Eliminating duplicate rows from the report

In Prompted Query, you can request that rows with duplicate information not appear in your report. For example, if you create a query to show all departments that have salespeople, based on the sample tables, the report will show duplicate rows for each department with more than one salesperson.

Figure 42. The report shows duplicate rows for several departments.

+--------------------------------------------------------------------------------+
|     DEPT  DIVISION                                                             |
|   ------  ----------                                                           |
|       15  EASTERN                                                              |
|       20  EASTERN                                                              |
|       38  EASTERN                                                              |
|       38  EASTERN                                                              |
|       42  MIDWEST                                                              |
|       51  MIDWEST                                                              |
|       51  MIDWEST                                                              |
|       66  WESTERN                                                              |
|       66  WESTERN                                                              |
|       66  WESTERN                                                              |
|       84  WESTERN                                                              |
|       84  WESTERN                                                              |
+--------------------------------------------------------------------------------+

To eliminate duplicate rows:

1. On the Specify panel, select Duplicate Rows. The Duplicate Rows panel displays.

   Figure 43. The Duplicate Rows panel
   

   +--------------------------------------------------------------------------------+
   | PROMPTED QUERY                                       MODIFIED  LINE    1       |
   |                                     +---------------------------------------+  |
   |   Tables:                           |            Duplicate Rows             |  |
   |     Q.STAFF(A)                      |                                       |  |
   |     Q.ORG(B)                        | Select one of the following.          |  |
   |                                     |                                       |  |
   |   Join Tables:                      | Keep. . 2 1. Duplicate rows.          |  |
   |     A.DEPT And B.DEPTNUMB           |           2. Single copy of each row. |  |
   |                                     +---------------------------------------+  |
   |   Columns:                          | F1=Help  F12=Cancel                   |  |
   |     DEPT                            +---------------------------------------+  |
   |     DIVISION                                                                   |
   |                                                                                |
   |   Row Conditions:                                                              |
   |     If JOB Is Equal To 'SALES'                                                 |
   |                                                                                |
   |   Duplicate Rows:                                                              |
   |   > ...                                                                        |
   |                                                                                |
   |   *** END ***                                                                  |
   |                                                                                |
   +--------------------------------------------------------------------------------+

2. Select Single copy of each row. The Prompted Query panel displays with your query in the echo area. The Specify panel does not display, because eliminating duplicate rows is the last step in creating a prompted query. Press the Specify key to display the Specify panel if you want to work on your query again.

   When you run the query, QMF displays a report that shows one row for each department, as shown in Figure 44.

   Figure 44. The report shows only one row for each department.
   

   +--------------------------------------------------------------------------------+
   |     DEPT  DIVISION                                                             |
   |   ------  ----------                                                           |
   |       15  EASTERN                                                              |
   |       20  EASTERN                                                              |
   |       38  EASTERN                                                              |
   |       42  MIDWEST                                                              |
   |       51  MIDWEST                                                              |
   |       66  WESTERN                                                              |
   |       84  WESTERN                                                              |
   |                                                                                |
   +--------------------------------------------------------------------------------+