In Prompted Query, you can request that rows with duplicate
information not appear in your report.
About this task
If you create a query that is based on the sample tables
that shows all departments that have salespeople, the report will
show duplicate rows for each department with more than one salesperson.
Such a report is shown in the following figure:
Figure 1. 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
Procedure
To eliminate duplicate rows:
On the Specify panel, select Duplicate
Rows.
The Duplicate Rows panel
displays, as shown in the following figure.Figure 2. 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 ***
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 the following figure.
Figure 3. 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