You can select specific rows to display on your report. Selecting rows limits, or creates a subset of, the data in a table. You select rows by creating a row condition.
To select rows:
The Row Conditions panel displays.
Figure 29. 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 that contains data from which you want to choose a limited selection. For example, you might want to see only the employees who are clerks. To do this, you select the rows where the value in the JOB column is CLERK. You can use any column in the table, even if you do not display it in the final report.
Figure 30. 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 complete the row condition by selecting the specific values you want to see from the column you previously selected. In this example, you want to select only rows where JOB is equal to clerk.
Select the verb and comparison operator that define the relationship between the column and the values you want to see on your report. For this example, you need the verb, Is, and comparison operator, Equal to, which are already selected.
You can specify more than one value on this panel. Enter the values on separate lines. If a character string contains a special character, such as a hyphen (-), enclose the character string in quotation marks. For example, if you are looking for an employee with a hyphenated name, such as Smith-Wiggins, type the name on the Equal To panel with single quotation marks around it:
'Smith-Wiggins'
Entering it this way ensures that the database will not interpret the hyphen as a minus sign, subtracting Wiggins from Smith. For a list of special characters, see the QMF Reference, SC26-4716-05.
Figure 31. Enter the value to use in selecting rows.
+--------------------------------------------------------------------------------+ | 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. Notice that in the echo area, Prompted Query has put single quotations around CLERK because it consists of character data.
Figure 32. QMF displays the row condition you created.
+--------------------------------------------------------------------------------+ | PROMPTED QUERY MODIFIED LINE 1 | | +------------------------+ | | Tables: | Specify | | | Q.STAFF | | | | | Select an item. | | | Columns: | | | | NAME | 1. Tables... | | | DEPT | 2. Columns... | | | JOB | 3. Row Conditions... | | | SALARY | 4. Sort... | | | COMM | 5. Duplicate Rows... | | | +------------------------+ | | Row Conditions: | F1=Help F12=Cancel | | | If JOB Is Equal To 'CLERK' +------------------------+ | | | | *** END *** | | | | | +--------------------------------------------------------------------------------+