Reference

List Variables

You can use list variables to build a delimited string of values. They are particularly useful in helping you construct an SQL query with multiple items like those found in some WHERE or HAVING clauses.

The blanks are significant. Usually you want to have a blank space on both sides of the value. Most queries use Boolean or mathematical operators (for example, AND, OR, and >). See LIST Statement for syntax and more information.

Example 1: Use of conditional, hidden, and list variables

%HTML(INPUT){
<FORM METHOD="POST" ACTION="/cgi-bin/db2www/example2.max/report">
Select one or more cities:<BR>
<INPUT TYPE="checkbox" NAME="conditions" VALUE="$$(cond1)">Sao Paulo<BR>
<INPUT TYPE="checkbox" NAME="conditions" VALUE="$$(cond2)">Seattle<BR>
<INPUT TYPE="checkbox" NAME="conditions" VALUE="$$(cond3)">Shanghai<BR>
<INPUT TYPE="submit" VALUE="Submit Query">
</FORM>
%}
 
%DEFINE{
DATABASE="custcity"
%LIST " OR " conditions
cond1="cond1='Sao Paolo'"
cond2="cond2='Seattle'"
cond3="cond3='Shanghai'"
whereClause= ? "WHERE $(conditions)" : ""
%}
 
%FUNCTION(DTW_SQL) mySelect(){
SELECT name, city FROM citylist
$(whereClause)
%}
 
%HTML(REPORT){
@mySelect()
%}

If no boxes are checked in the HTML form, conditions is null, so whereClause is also null in the query. Otherwise, whereClause has the selected values separated by the Boolean operator OR. For example, if all three cities are selected, the SQL query is:

SELECT name, city FROM citylist
WHERE cond1='Sao Paolo' OR cond2='Seattle' OR cond3='Shanghai'

Example 2: Value separators

%DEFINE %LIST " | " VLIST
%REPORT{
%ROW{
<EM>$(ROW_NUM):</EM> $(VLIST)
%}
%}

The table processing variable VLIST uses two quotes and an OR bar, ( |), as a value separator in this example. The string of values are separated by the value in quotes.