You can use lookup variables in a query to present the user with a drop-down menu of possible substitution values for the variable at run time. Lookup variables create a drop-down menu from the saved query or results set that you use in the variable name. In the Query or result, the lookup variable will be the first column returned and it will be separated by a space form the other columns if there are any. Depending on the data type, it will be quoted automatically. Lookup variables only work with saved queries or results.
To write an SQL query with lookup variables:
1. In the desired connection branch of the DB2 Web Query navigation tree, click Queries.
The Queries menu appears.
2. From the Queries drop-down menu, select New.
The Query page opens.
3. In the Result Name field, type the name of the results set.
4. In the Max Rows field, enter the maximum number of rows to return in the results set.
5. In the Enter an SQL Statement field, type an SQL statement. To include lookup variables in your query, use one of the following formats:
[q.groupname.queryname.n]
[r.groupname.resultname]
[qm.groupname.queryname.n]
[rm.groupname.resultname]
Where n is the query number. By entering a query number, you can link a query to multiple results sets. If no number is entered the default, result set 1, will be used.
Use the name of the query or results set from which you want to draw the drop-down menu. DB2 Web Query uses the first column as the list of possible substitution values for the variable. DB2 Web Query concatenates all columns after the first into display values in the drop-down menu and uses the corresponding values in the first column as the substitution values.
Lookup variables beginning with "qm." or "rm." allow you to select multiple values for the variable. Lookup variables beginning with "q." or "r." only allow you to select one value. The target for multiple select lookup variables is an IN clause.
Tip: Query lookup variables (q. or qm.) fetch their values from the database every time the query is loaded. Result lookup variables (r. or rm.) draw their values from a static results set file.
Note: When referencing a look-up variable that resolves to a non-numeric value in a query, IBM DB2 Web Query will enclose the value in single quotes. For example: WHERE A.NAME=’joe’