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, PowerBuilder 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 (that is, the association was made in the Database painter),
if possible, the edit style is used in the grid. Drop-down list
boxes are used for columns with code tables and columns using the
CheckBox and RadioButton edit styles.
SQL operators supported in Quick Select
You can use these SQL relational
operators in the 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 wildcard
asterisk (*) used in many applications, matches multiple
characters. For example,Good%matches
all names that begin withGood. -
The underscore character (_) matches a
single character. For example,Good _ _ _matches
all seven-letter names that begin withGood.
Use IN to
compare and include a value that is in a set of values. Use NOT
IN to compare and include values that are not in a set
of values. For example, the following clause selects all employees
in department 100, 200, or 500:
|
1 |
SELECT * from employee<br>WHERE dept_id<span> IN (100, 200, 500)</span> |
Using NOT IN in this clause would exclude
employees in those departments.
Connection operators
You can use the OR and AND logical
operators to connect expressions.
PowerBuilder makes some assumptions based on how you specify
selection criteria. When you specify:
-
Criteria for more than one column on one line
PowerBuilder 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
PowerBuilder 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:
|
Operator |
Meaning |
|---|---|
|
OR |
The row is selected if one expression OR another |
|
AND |
The row is selected if one expression AND another |
This technique is particularly handy when you want to retrieve
a range of values in a column. See example 6 below.