In this case 0=0 will always evaluate as true, therefore, the query will return every row.
We can combine the logic of each
WHERE with an
OR into a single query, and substitute in our Dropdown value. The SQL Query binding would then look something like this:
This way, the WHERE clause will only be true when the filter column matches with our selected option, or it will return all rows, if we setup our "all" option to have a value of 0.
Example - Filtering Data on an Area Number
We can put together an easy example on how this might with data that stores what machines are in which areas. Our data table should look something like this:
We can then put together the query and components necessary to get this working:
- Create a new Named Query. Set up security to fit your needs, and name it appropriately. For more information on creating Named Queries, see Using Named Queries - Example.
- Create a single Value type Parameter that is an Int2 data type. I called mine dropdownValue
Add in the combined query that we went over above, but use the machine table name and column names.
- Create a new Main Window and add a Power Table component and a Dropdown component to the window.
On the Dropdown's Data property, create a dataset that looks like this:
Value Label 1 Area 1 2 Area 2 3 Area 3 0 All Areas
On the Power Table component, create a Named Query Binding on the Data property.
- Select the Named Query that was created in step 1.
- For the parameter, bind its value to the Selected Value property of the Dropdown component, and the click the OK button.
- Put the Designer into preview mode and try out the Dropdown Filter. You will see that selecting a different value in the dropdown filters the data coming back so that only certain rows are shown.