Database Guide
As you learned in Using a host variable, you can compose dynamic queries using host
variables. VisualAge also provides a way to make an entire
WHERE clause dynamic, operator and all, which enables you to restrict the
result table rows of a query in even more flexible ways.
After following the steps in this section, you will have created the
following query. This query must be created manually.
SELECT * FROM STAFF :whereClause
This query will select all rows from the STAFF table that meet the
conditions of the variable WHERE clause.
- Open the Composition Editor on a new visual part and add a Multi-row Query
part to the free-form surface.
- Open the Multi-row Query Settings window by double clicking on the
multi-row query part.
- After selecting a connection specification and access set for the query,
turn to page 2 of the Query Spec notebook tab.
- Select the Manual create push button.
The SQL Details window is displayed and looks like this:

- Type a name, description, and the following SQL statement into the fields
on this window and then select the Apply push button.
SELECT * FROM STAFF :whereClause
Note: | You must enter :whereClause as shown. The dynamic
WHERE clause will not work otherwise.
|
Using a query with a dynamic WHERE clause
In the Composition Editor, notice that the pop-up menu for your multi-row
query part now includes the attribute whereClause, the name of the
host variable you used for the dynamic WHERE clause.
Begin constructing your visual part as follows:
- Name your window Staff Members.
- Tear off the result table of your multi-row query part and create a quick
form of the result table in your window.
- Add an event-to-action connection from the window's
aboutToOpenWidget event to the database query's
executeQuery action.
Your Composition Editor will look like this:

Complete your visual part as follows:
- Add a variable to the free-form surface and give it the name
whereClauseIndex.
- From the variable's pop-up menu, select Change Type and
enter Integer as the new type.
- From the variable's pop-up menu, select Promote Part
Feature. In the Promote features window, select self
from the attribute list, and then select the Promote
push button. Close the Promote features window.
- Switch to the Script Editor and creating the following script:
initializeWhereClause
| where |
((self subpartNamed: 'whereClauseIndex') value == 1)
ifTrue:
[ where := 'WHERE (STAFF.SALARY > (SELECT AVG(STAFF.SALARY) FROM STAFF))'].
((self subpartNamed: 'whereClauseIndex') value == 2)
ifTrue:
[ where := 'WHERE (STAFF.SALARY < (SELECT AVG(STAFF.SALARY) FROM STAFF))'].
(self subpartNamed: 'Multi-row Query1')
valueOfAttributeNamed: 'whereClause' put: where.
This script sets up values for the whereClauseIndex
variable. In the next section, you will see how to assign each value to
a radio button.
- Switch to the Composition Editor and create an event-to-script connection
from the window's aboutToOpenWidget event to the
initializeWhereClause script.
- Select Reorder Connections From from the pop-up menu on the
window and move your event-to-script connection before your event-to-action
connection so that your dynamic WHERE clause will initialize before the query
executes.
- Save your work.
With this new visual part, you have a window that can be reused from
different places in an application. By passing the value 1
to the window in the whereClauseIndex attribute, you can restrict
the result table to staff entries with a higher-than-average salary. By
passing the value 2, you can restrict the result table to staff
entries with a lower-than-average salary.
Testing the dynamic WHERE clause
Where and how your window is opened depends on the design and needs of your
application, but for this example, create a test case by creating a new visual
part that looks like this:

- Name your window Staff Salary Reports.
- Lay out the radio-button group and push button, and label them as
shown.
- Add a View Wrapper part from the Model category to the free-form surface,
give it the name Staff Members Window, and specify the class name
of your Staff Members visual part as the type.
For more information on view wrappers, see the VisualAge Smalltalk
User's Guide.
- Create an attribute-to-attribute connection from the
selectionIndex attribute of the radio-button group to the
whereClauseIndex attribute of the view wrapper part.
- Create an event-to-action connection from the clicked event of
the push button to the openWidget action of the view wrapper
part.
- Save your work and test this visual part by selecting each radio button,
clicking OK, and verifying that the Staff Members window appears
and displays the correct data.
[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]