Next, you select the rows to display on your report. For this lesson, select only the rows for employees who are clerks.
In QMF, selecting specific rows to display is called creating a row condition.
To select rows:
The Row Conditions panel displays, as shown in Figure 10.
Figure 10. The Row Conditions panel
+--------------------------------------------------------------------------------+ | PROMPTED QUERY MODIFIED LINE 1 | | +--------------------------------------------+ | | Tables: | Row Conditions | | | Q.STAFF | | | | | Begin a condition by selecting one column, | | | Columns: | or by entering an expression or function. | | | NAME | 1 to 8 of 8 | | | DEPT | * Q.STAFF | | | JOB | 2. ID | | | SALARY | 3. NAME | | | COMM | 4. DEPT | | | | 5. JOB | | | Row Conditions: | 6. YEARS | | | > If... | 7. SALARY | | | | 8. COMM | | | *** END *** | | | | | Expression (A+B, etc.)... | | | +--------------------------------------------+ | | | F1=Help F5=Describe F7=Backward | | | | F8=Forward F12=Cancel | | | +--------------------------------------------+ | | | +--------------------------------------------------------------------------------+
To create a row condition, select a column on which to base your row condition. You can use any column in the table, even if you are not displaying it on the report.
In this example, you want to display only rows where the job is clerk, so you will select the JOB column.
The Comparison Operators panel displays:
Figure 11. The Comparison Operators panel
+--------------------------------------------------------------------------------+ | PROMPTED QUERY MODIFIED LINE 1 | | +--------------------------------------------+ | | Tables: | Row Conditions | | | Q.STAFF +------------------------------------------+ | | | | Comparison Operators | | | | Columns: | | | | | NAME | Select a verb and a comparison. | | | | DEPT | | | | | JOB | Verb. . . . 1 1. Is | | | | SALARY | 2. Is not | | | | COMM | 1 to 7 of 10 | | | | | Comparison 1 1. Equal to... | | | | Row Conditions: | 2. Less than... | | | | > If JOB... | 3. Less or equal to... | | | | | 4. Greater than... | | | | *** END *** | 5. Greater or equal to... | | | | | 6. Between... | | | | | 7. Starting with... | + | | +------------------------------------------+ | | | | F1=Help F7=Backward F8=Forward | | | | | F12=Cancel | + | | +------------------------------------------+ | | | +--------------------------------------------------------------------------------+
On the Comparison Operators panel, you will create a row condition in which you only see rows where JOB is equal to clerk. To do this, you select 1, Is, and then select 1, Equal to. These choices are already selected for you.
The Equal To panel displays.
+--------------------------------------------------------------------------------+ | PROMPTED QUERY MODIFIED LINE 1 | | +--------------------------------------------+ | | Tables: | Row Conditions | | | Q.STAFF +------------------------------------------+ | | | | Comparison Operators | | | | Columns: +----------------------------------------------------------+ | | | | NAME | Equal To | | | | | DEPT | | | | | | JOB | Type one or more values, column names, or expressions. | | | | | SALARY | | | | | | COMM | . . ( clerk ) | | | | | | Or. . ( ) | | | | | Row Condit | Or. . ( ) | | | | | > If JOB I | Or. . ( ) | | | | | | Or. . ( ) | | | | | *** END ** | Or. . ( ) | | | | | +----------------------------------------------------------+ | | | | | F1=Help F4=List F5=Show Field F12=Cancel | | + | | +----------------------------------------------------------+ + | | | | F1=Help F7=Backward F8=Forward | | | | | F12=Cancel | + | | +------------------------------------------+ | | | +--------------------------------------------------------------------------------+
The row condition you created is displayed in the echo area, and the Specify panel displays again.
The Prompted Query panel displays. Your query is displayed in the echo area, as shown in Figure 13.
Figure 13. QMF displays your query on the Prompted Query panel.
+--------------------------------------------------------------------------------+ | PROMPTED QUERY MODIFIED LINE 1 | | | | Tables: | | _ Q.STAFF | | | | Columns: | | _ NAME | | _ DEPT | | _ JOB | | _ SALARY | | _ COMM | | | | Row Conditions: | | _ If JOB Is Equal To 'CLERK' | | | | *** END *** | | | | | | | | | | 1=Help 2=Run 3=End 4=Show SQL 5=Change 6=Specify | | 7=Backward 8=Forward 9=Form 10=Insert 11=Delete 12=Report | | OK, CANCEL command executed successfully. | | COMMAND ===> SCROLL ===> PAGE | +--------------------------------------------------------------------------------+
The following report displays:
Figure 14. QMF displays the data from your query.
+--------------------------------------------------------------------------------+ | REPORT LINE 1 POS 1 79 | | | | | | | | NAME DEPT JOB SALARY COMM | | --------- ------ ----- ---------- ---------- | | JAMES 20 CLERK 13504.60 128.20 | | NGAN 15 CLERK 12508.20 206.60 | | NAUGHTON 38 CLERK 12954.75 180.00 | | YAMAGUCHI 42 CLERK 10505.90 75.60 | | KERMISCH 15 CLERK 12258.50 110.10 | | ABRAHAMS 38 CLERK 12009.75 236.50 | | SNEIDER 20 CLERK 14252.75 126.50 | | SCOUTTEN 42 CLERK 11508.60 84.20 | | LUNDQUIST 51 CLERK 13369.80 189.65 | | WHEELER 51 CLERK 14460.00 513.30 | | BURKE 66 CLERK 10988.00 55.50 | | GAFNEY 84 CLERK 13030.50 188.00 | | | | | | 1=Help 2= 3=End 4=Print 5=Chart 6=Query | | 7=Backward 8=Forward 9=Form 10=Left 11=Right 12= | | | +--------------------------------------------------------------------------------+