Using a dynamic WHERE clause
As you learned in Using a host variable, you can compose dynamic queries using host variables. VA Smalltalk 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.
Constructing the query
1. Open the Composition Editor on a new visual part and add a Multi-row Query part to the free-form surface.
2. Open the Multi-row Query Settings window by double clicking on the multi-row query part.
3. After selecting a connection specification and access set for the query, turn to page 2 of the Query Spec notebook tab.
4. Select the Manual create push button.
The SQL Details window is displayed and looks like this:
5. 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:
1. Name your window Staff Members.
2. Tear off the result table of your multi-row query part and create a quick form of the result table in your window.
3. 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:
1. Add a variable to the free-form surface and give it the name whereClauseIndex.
2. From the variable's pop-up menu, select Change Type and enter Integer as the new type.
3. 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.
4. 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.
5. Switch to the Composition Editor and create an event-to-script connection from the window's aboutToOpenWidget event to the initializeWhereClause script.
6. 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.
7. 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:
how
1. Name your window Staff Salary Reports.
2. Lay out the radio-button group and push button, and label them as shown.
3. 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 Visual Programming User Guide.
4. Create an attribute-to-attribute connection from the selectionIndex attribute of the radio-button group to the whereClauseIndex attribute of the view wrapper part.
5. Create an event-to-action connection from the clicked event of the push button to the openWidget action of the view wrapper part.
6. 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.
Last modified date: 07/25/2020