Progress
Results User’s Guide
for Windows
Using Data Selection to Refine a Query
You can use Data Selection to create a WHERE clause automatically or manually. The following sections describe these techniques.
Using Data Selection to Create a WHERE Clause Automatically
Follow these steps to refine a query using Data Selection:
- Choose Data
Selection. If there is more than one table available, the Available Tables dialog box appears.
- Select the table that contains the fields you want to use to limit the query, then choose OK.
If you are creating a WHERE clause that contains more than one expression and these expressions contain fields from different tables, create the expressions in increments. Select the first table and create the first expression, then select another table to create the second expression, and so on until you complete the clause.
The Data Selection dialog box appears:
![]()
- If you want Results to prompt you to set your selection criteria at run time, activate the Ask At Runtime toggle box.
By default, Data Selection does not use Ask At Runtime. This means that the values you enter in the WHERE clause are the only values used to select data. To change the selection criteria, you must re-enter Data Selection and create a new WHERE clause. Ask At Runtime lets you modify the values for one or more fields in the WHERE clause each time you run the query. For example, you can tell Results to prompt you for a value to compare to the State field each time you run a query. For more information, see the "Setting Data Selection at Run Time" section.
- Double-click the field you want to use to refine the selection criteria.
Results displays the field in the Selection Criteria text box at the bottom of the screen.
- Select the comparison operator you want to use to compare a value to the field. See "Comparison Operators," for a description of these operators.
One of the following occurs:
- If you activated the Ask At Runtime toggle box, the Ask At Run Time Prompt dialog box appears:
![]()
Type a question that will prompt you to enter a value for the field when you open or run the query. For example, if you are comparing a value to the State field, you might type Enter a State. Then choose OK.
- If you did not activate the Ask At Runtime toggle box, the Enter Constant dialog box appears:
![]()
Type the value you want to compare to the field, then choose OK. Do not place quotation marks around the value. If quotation marks are required, Results will automatically insert the quotation marks for you when it formats the WHERE clause.
Results formats the WHERE clause and displays it in the Selection Criteria area.
- Do one of the following:
- If the WHERE clause is complete, choose OK and redisplay the query based on your selection criteria.
- If you want to add another expression to the WHERE clause based on a field in the current table, determine whether you want to use the AND or the OR logical operator to join the next expression to the expression you just created. Then choose either the AND button or the OR button at the bottom of the dialog box.
The AND operator means both the combined expressions must be true for the record to be selected. If you use State = “MA” AND Balance > 0, Results selects only those records where the customer is from Massachusetts and the customer has a positive balance.
The OR operator means only one of the combined expressions must be true for the record to be selected. If you use State = “MA” OR Cust-Num < 50, Results selects all the records between 1 and 49 plus those records where the customer is from Massachusetts.
- If the expression you want to add to the WHERE clause is based on a field in a different table, choose OK to save the WHERE clause and redisplay the query based on your selection criteria. Then start with Step 1 to create the expression.
When you open the table to create the new expression, the previous expression is not displayed in the Selection Criteria text box. Each table displays its own portion of the WHERE clause only. Results automatically uses the AND operator to join expressions from different tables together and create the WHERE clause for you.
- Repeat Steps 3 through 6 to add an expression to the WHERE clause based on a field in the same table.
Using Data Selection to Create a WHERE Clause Manually
Once you are familiar with automatically creating WHERE clauses using Data Selection, you can create and edit WHERE clauses manually.
Follow these steps to create a WHERE clause manually:
- Choose Data
Selection.
The Available Tables dialog box appears.
- Select the table that contains the fields you want to use to limit the query, then choose OK.
If you are creating a WHERE clause that contains more than one expression and these expressions contain fields from different tables, create the expressions in increments. Select the first table and create the first expression, then select another table to create the second expression, and so on until you complete the clause.
The Data Selection dialog box appears.
- Position the pointer in the Selection Criteria text box and click to place the insertion point in the upper-left corner of the editor.
- Type the WHERE clause in the Selection Criteria editor. See "Comparison Operators," for a description of the comparison operators you can use to compare values within the WHERE clause.
Follow these guidelines while creating your WHERE clause:
- Use the complete name for each field in the WHERE clause. A complete field name consists of the database name, the table name, and the field name separated by periods (.). For example, type sports.Customer.City where sports is the name of the database, customer is the name of the table, and city is the name of the field.
- Enclose all text strings in quotation marks (“ ”). For example,
sports.customer.city BEGINS “M”.
![]()
- When you are done entering the WHERE clause, choose the Now button to check the syntax for the clause. An alert box appears.
- Choose Cancel. If the syntax is incorrect, fix the error and recheck the syntax.
- Choose OK to save the WHERE clause and close the Data Selection dialog box.
Setting Data Selection at Run Time
When you use Data Selection to create a WHERE clause, you can choose the Ask At Runtime option. Choosing Ask At Runtime means that instead of entering a comparison value when you create the WHERE clause, Results prompts you to enter the value when you run the query. This means that you can enter the value when you run the query in an application outside of Results or when you change to the Browse or Form view. When you use the Report, Labels, and Export views, Results does not prompt you to enter a value until you either print the query or use print preview.
To enter a value, type the value in the Ask At Runtime dialog box, then click OK. Note that you do not have to place quotation marks around the value. If the value requires quotation marks, Results automatically inserts them:
![]()
To reset the value from within the Form and Browse views, choose Data
Re-Ask Questions, then type a value in the Ask At Runtime dialog box. To reset the value from within the Report, Labels, and Export view, simply reprint the query or use Print Preview.
Removing a WHERE Clause Created Using Data Selection
Follow these steps to remove a WHERE clause that you created using Data Selection:
- Choose Data
Selection.
- Select the table that contains the expression you want to remove. The Data Selection dialog box appears:
![]()
- Select the WHERE clause, then press DELETE to remove the clause.
- Choose OK to save your changes and close the Data Selection dialog box.
- If you created an expression for another table, repeat Steps 1 through 4 for every table that contains an expression.
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |