Reference

Conditional Variables

The value of a conditional variable is conditionally set based on the value of another variable or string. This is also called a ternary operation.

The syntax of conditional variable is:

test ? trueValue : falseValue

Where:

test

Is a condition to test.

trueValue

Is the value to use if the test is true.

falseValue

Is the value to use if the test is false.

Example 1: A conditional variable defined with two possible values

varA = varB ? "value_1" : "value_2"

If varB exists, varA=value_1, otherwise varA=value_2.

Example 2: A conditional variable defined with a variable reference

varname = ? "$(value_1)"

In this case, varname is null if value_1 is null, otherwise varname is set to value_1.

Example 3: A conditional variable used with a LIST statement and WHERE clause

%DEFINE{
%list " AND " where_list
where_list    =  ? "custid = $(cust_inp)"
where_list    =  ? "product_name LIKE '$(prod_inp)%'"
where_clause  =  ? "WHERE $(where_list)"
%}
 
%FUNCTION(DTW_SQL) mySelect() {
   SELECT * FROM prodtable $(where_clause)
%}

Conditional and LIST variables are most effective when used together. The above example shows how to set up a WHERE clause in the DEFINE block. The variables cust_inp and prod_inp are HTML input variables passed from the Web browser, usually from an HTML form. The variable where_list is a LIST variable made of two conditional statements, each statement containing a variable from the Web browser.

If the Web browser returns values for both variables cust_inp and prod_inp, for example, IBM and 755C, the where_clause is:

WHERE custid = IBM AND product_name LIKE '755C%'

If either variable cust_inp or prod_inp is null or not defined, the WHERE clause changes to omit the null value. For example, if prod_inp is null, the WHERE clause is:

WHERE custid = IBM

If both values are null or undefined, the variable where_clause is null and no WHERE clause appears in SQL queries containing $(where_clause).