DW SQL Runtime Variables

Report · Aug 14, 2008

When you define a record set within DW, there is an option to define selection variables (e.g., using colname). For example, "SELECT * from Cust_table Where cust_id = colname". Then at runtime, one can supply a value for colname as a form or URL variable. Works great.

Here is my problem. In trying to filter the result set that shows in a table within a form, I can add a selection variable populated by a pull down menu as a form variable to select rows having a status of "O" Open or "C" Closed status. I simply have these as dynamic list values. But, how do I get both Open and Closed? What value if any can I send to the SQL through DW to get them all (as if there were no WHERE clause at all)? Easy to do within the code and within SQL itself through PHP, but is there a way through the DW interface to do this? I can check in the code to see if the selection was "A" for all, then modify the SQL, but it seems like there should be an easy way to do this.

A clumsy way is to use a NOT = condition in the SQL WHERE (e.g., if NOT 'O' then get closed, if NOT "C" then it get Open, if NOT "x" then it gets everything), but this seems a little awkward.

Thanks.

Report · Aug 18, 2008

DreamerJim wrote:
> When you define a record set within DW, there is an option to define selection
> variables (e.g., using colname). For example, "SELECT * from Cust_table Where
> cust_id = colname". Then at runtime, one can supply a value for colname as a
> form or URL variable. Works great.
>
> Here is my problem. In trying to filter the result set that shows in a table
> within a form, I can add a selection variable populated by a pull down menu as
> a form variable to select rows having a status of "O" Open or "C" Closed
> status. I simply have these as dynamic list values. But, how do I get both
> Open and Closed? What value if any can I send to the SQL through DW to get
> them all (as if there were no WHERE clause at all)? Easy to do within the code
> and within SQL itself through PHP, but is there a way through the DW interface
> to do this? I can check in the code to see if the selection was "A" for all,
> then modify the SQL, but it seems like there should be an easy way to do this.
>
> A clumsy way is to use a NOT = condition in the SQL WHERE (e.g., if NOT 'O'
> then get closed, if NOT "C" then it get Open, if NOT "x" then it gets
> everything), but this seems a little awkward.
>
> Thanks.
>

hmmm

WHERE (var IS NULL or status = var)

possibly :)

Dooza