Adding selection criteria in the SQL designer
Selection criteria in a query specifies which rows of data are included in the query results. In the SQL designer, you enter selection criteria into the Criteria page of the Query notebook.
Specifying selection criteria
In the Criteria page grid, choose the type of criteria you want by right-clicking in the grid and selecting the appropriate criteria from the grid’s context menu. You may choose from Simple Equation, SQL Expression, and EXISTS. Each type is described below.
Enter criteria into the row according to the type of criteria you have chosen.
Simple Equation
A simple equation compares the values of two values for each row of data. For example,
CustNo >= 1000
The values can be either a field name, constant value or any valid SQL expression. String and date constant values must be surrounded by single quotes.
When defining a simple equation the grid has three columns: Field or Value, Compare, and Field or Value.
To enter a simple equation:
Enter the first field or value you wish to compare into the first Field or Value column. This can be done by either dragging a field from a table window in the table pane and dropping it onto the Field or Value column, selecting a field from the drop-down list box, or entering a constant value or valid SQL expression into the Field or Values column.
Select the appropriate comparison operator from the Compare column drop-down list box. You can choose from =, >, <, >=, <=, <>, LIKE, IN, BETWEEN, NOT BETWEEN, IS NULL, or IS NOT NULL.
Enter the field or value you wish to compare to the first into the second Field or Value column. This can be done by either dragging a field from a table window in the table pane and dropping it onto the Field or Value column, selecting a field from the drop-down list box, or entering a constant value or valid SQL expression into the Field or Values column.
Remember to press Enter after entering the last element of your equation.
SQL Expression
Enter an SQL expression directly into the SQL Expression column. For example,
((CustNo < 2000) OR (CustNo > 3000))
String and date constant values must be surrounded by single quotes.
EXISTS Clause
Adding an EXISTS clause returns True when the subquery produces at least one row of query results.
When a row has this type of selection criteria, the row in the grid has two columns: Operator and SQL Expression. Select EXISTS from the Operator column. You can now enter an SQL expression to see if any rows are produced.
The following example returns all the companies who have placed orders:
SELECT Company FROM Customer.db WHERE EXISTS (SELECT * FROM Orders WHERE Orders.CustNo = Customer.CustNo)
In the preceding example, you would enter the statement following the ‘EXISTS’ into the SQL Expression column.
String and date constant values must be surrounded by single quotes.