You can include variables and comments in an SQL query. You can also use lookup variables, which refer to other queries or results to produce a drop-down menu of substitution values.
To write an SQL query with variables and comments:
1. In the desired connection branch of the DB2 Web Query navigation tree, click Queries.
The Queries menu appears.
2. From the drop-down menu, select New.
The Query page appears.
3. In the Result Name field, type the name of the results set.
Tip: DB2 Web Query provides a default results set name. You can replace this name with one that you prefer.
4. In the Max Rows field, enter the maximum number of rows to return in the results set.
Tip: To return all rows to the results set, enter "0" in the Max Rows field.
5. In the Enter an SQL Statement field, type an SQL statement. To include variables in your query, use one of the following formats:
?
:variable
[variable]
&variable
For example, in the following SQL statement, &userid is a variable:
select * from table.sample where user=&userid
You can also use any of the following system variables:
wqsys.time |
Substitutes the current time. |
wqsys.date |
Substitutes the current date. |
wqsys.timestamp |
Substitutes the current timestamp. |
wqsys.user |
Substitutes the user name under which you are logged in. |
wqsys.sqlid |
Substitutes the SQL ID under which you are logged in. |
wqsys.database |
Substitutes the name of the database you are accessing. |
6. To include lookup variables in your query, use one of the following formats:
[q.groupname.queryname]
[r.groupname.resultname]
[qm.groupname.queryname]
[rm.groupname.resultname]
Use the name of the query or result you want to draw the drop-down menu from. Lookup variables beginning with "qm." or "rm." allow the user to select multiple values for the variable. Lookup variables beginning with "q." or "r." only allow the user to select one value.
7. To include comments in your query, use one of the following formats:
-- comment
// comment
Note: Only the text following the comment symbols is treated as a comment. Any text preceding the comment symbols on the same line is treated as part of the SQL statement.