Chapter 17: Defining DataWindow Objects

Specifying selection criteria

You can enter selection criteria in the grid to specify which rows to retrieve. For example, instead of retrieving data about all employees, you might want to limit the data to employees in Sales and Marketing, or to employees in Sales who make more than $80,000.

As you specify selection criteria, PocketBuilder builds a WHERE clause for the SELECT statement.

To specify selection criteria:

Click the Criteria row below the first column for which you want to select the data to retrieve.
Enter an expression, or if the column has an edit style, select or enter a value.

If the column is too narrow for the criterion, drag the grid line to enlarge the column. This enlargement does not affect the column size in a DataWindow object.
Enter additional expressions until you have specified the data you want to retrieve.

About edit styles If a column has an edit style associated with it in the extended attribute system tables (an association made in the Database painter), the edit style is used in the grid selection criteria where possible. Drop-down list boxes are used for columns with code tables and columns that use the CheckBox and RadioButton edit styles.

SQL operators supported in Quick Select

You can use these SQL relational operators in the retrieval criteria:

**Table 17-2: SQL relational operators used in retrieval criteria**
Operator	Meaning
=	Is equal to (default operator)
>	Is greater than
<	Is less than
< >	Is not equal to
> =	Is greater than or equal to
< =	Is less than or equal to
LIKE	Matches this pattern
NOT LIKE	Does not match this pattern
IN	Is in this set of values
NOT IN	Is not in this set of values

Because = is the default operator, you can enter the value 100 instead of = 100, or the value New Hampshire instead of = New Hampshire.

Comparison operators

You can use the LIKE, NOT LIKE, IN, and NOT IN operators to compare expressions.

Use LIKE to search for strings that match a predetermined pattern. Use NOT LIKE to find strings that do not match a predetermined pattern. When you use LIKE or NOT LIKE, you can use wildcards:

The percent sign (%), like the DOS wildcard asterisk (*), matches multiple characters. For example, Good% matches all names that begin with Good.
The underscore character ( _ ) matches a single character. For example, Good _ _ _ matches all seven-letter names that begin with Good.

Use IN to compare and include rows with values that fall within the set of values that you define. Use NOT IN to compare and exclude rows with values that do not fall within the set of values that you define. For an example using the IN relational criteria, see “Example 4”. For an example using the LIKE relational criteria, see “Example 6”.

Connection operators

You can use the OR and AND logical operators to connect expressions.

**Table 17-3: Logical operators for selection criteria expressions**
Operator	Meaning
OR	The row is selected if one expression OR another expression is true
AND	The row is selected if one expression AND another expression are true

PocketBuilder makes some assumptions based on how you specify selection criteria. When you specify:

Criteria for more than one column on one line, PocketBuilder assumes a logical AND between the criteria. A row from the database is retrieved if all criteria in the line are met.
Two or more lines of selection criteria, PocketBuilder assumes a logical OR. A row from the database is retrieved if the criterion in any of the lines is met.

To override these defaults, begin an expression with the AND or OR operator. For an example overriding the default logical operators, see “Example 5”.

This technique is particularly handy when you want to retrieve a range of values in a column.

View this book as PDF