Filtering rows
You can use WHERE and HAVING clauses and retrieval arguments in
the SQL SELECT statement for the DataWindow object to limit the data
that is retrieved from the database. This reduces retrieval time and
space requirements at runtime.
However, you may want to further limit the data that displays in
the DataWindow object. For example, you might want to:
-
Retrieve many rows and initially display only a subset
(perhaps allowing the user to specify a different subset of rows to
display at runtime) -
Limit the data that is displayed using DataWindow expression
functions (such as If) that are not valid in the SELECT
statement
Using filters
In the DataWindow painter, you can define filters to limit the
rows that display at runtime. Filters can use most DataWindow expression
functions or user-defined functions.
Filters do not affect which rows are retrieved
A filter operates against the retrieved data. It does not
re-execute the SELECT statement.
Defining a filter
To define a filter:
-
In the DataWindow painter, select Rows>Filter from the menu
bar.The Specify Filter dialog box displays:
-
In the Specify Filter dialog box, enter a boolean expression
that PowerBuilder will test against each retrieved row.If the expression evaluates to true, the row is displayed. You
can specify any valid expression in a filter. Filters can use any
non-object-level PowerScript function, including user-defined
functions. You can paste commonly used functions, names of columns,
computed fields, retrieval arguments, and operators into the
filter.International considerations
The formatting that you enter for numbers and currency in
filter expressions display the same way in any country. Changing
the regional settings of the operating system does not modify the
formatting displayed for numbers and currency at runtime.For information about expressions for filters, see the section called “Where you
use DataWindow expressions” in DataWindow Reference. -
(Optional) Click Verify to make sure the expression is
valid. -
Click OK.
Only rows meeting the filter criteria are displayed in the
Preview view.Filtered rows and updates
Modifications of filtered rows are applied to the database
when you issue an update request.
Removing a filter
To remove a filter:
-
Select Rows>Filter from the menu bar.
-
Delete the filter expression from the Specify Filter dialog
box, then click OK.
Examples of filters
Assume that a DataWindow object retrieves employee rows and three
of the columns are Salary, Status, and Emp_Lname. The following table
shows some examples of filters you might use.
|
To display these rows |
Use this filter |
|---|---|
|
Employees with salaries over $50,000 |
Salary > 50000 |
|
Active employees |
Status = ‘A’ |
|
Active employees with salaries over |
Salary > 50000 AND Status = ‘A’ |
|
Employees whose last names begin with |
left(Emp_Lname, 1) = ‘H’ |
Setting filters
dynamically
You can use the SetFilter and Filter methods in a script to
dynamically modify a filter that was set in the DataWindow painter. For
information about SetFilter and Filter, see the section called “SetFilter” in DataWindow Reference and
the section called “Filter” in DataWindow Reference.