Restriction: This topic applies to Windows environments only.
Some of the conditions on columns that can be added to a query cannot be expressed easily using the Query Conditions Grid. In these cases, you have two alternatives:
- Edit the SQL statement, in which case you can include any valid condition (including nested SELECTS).
- Include an extended search condition.
An extended search condition is simply another set of conditions that are appended to the Query Conditions Grid as AND conditions.
To define an extended search condition:
- Open a new or existing query.
- Click the Where (?) button on the toolbar.
SQLWizard displays the Search Condition dialog box. You compose a series of extended search conditions at the top of this dialog box, one per row.
- On the first row, the first drop-down list contains only the NOT qualifier. You should use this if you want to negate the condition you create on this row.
- The next field is used to specify the left-hand side of the expression. Move to this field and type in the expression, or compose it using the tools at the bottom of the dialog box. There are drop-down lists containing functions, as well as operators and column names that are used to build the expression.
- The next control is a drop-down list containing comparison operators, including SQL predicates such as BETWEEN and IN. Select an operator from this list.
- The next field is used to specify the right-hand side of the expression. Move to this field and complete the expression.
- The last control on the row is a button to add another condition row to the dialog box. Click it to add another condition row.
- Move to the next row and click the down-arrow button on the first drop-down list. While in the first row, the drop-down list only contains the NOT option, in the second and subsequent rows, this list contains connection operators AND and OR, with or without the NOT qualifier. Choose one of the connection operators.
- Complete the expression on this row as you did for the previous row.
- When you are finished with the extended search condition, click OK to accept the changes.
- Examine the Query Conditions Grid. You will see that a new area labeled AND has been added, showing the conditions that you just defined. If you want to make changes to the conditions, double click the area to redisplay the Search Condition dialog box.
If you want to add conditions that cannot be expressed through the Query Conditions Grid columns or the Search Condition dialog box, switch to the SQL view and edit the SQL statement directly. Note that once you have made changes to the statement in the SQL Edit window, you will not be able to switch back to the Query Design window without discarding the changes. Therefore, you should save your query in the Query Design window before switching to the SQL Edit window, and save it from the SQL Edit window using a different name if you make changes.
