WHERE Clause In A Dynamic Query

Cory Fifield -

Trouble viewing images? Click on images to enlarge.

 

In this article we cover the setting up dynamic queries for parameter selection that leverage a WHERE clause to filter the results. If you would like to download the sample View used in this demonstration, scroll down to the bottom of the page where there will be a download link available, or click here.

 

Why Use A WHERE Clause?

WHERE clauses are optional in SQL statements, but can be used to limit the number of rows affected by a SQL statement or returned by a query. A WHERE clause is used to extract specific results from a SQL statement, such as: SELECT, INSERT, UPDATE, or DELETE.

 

How To Create Dynamic Queries Using WHERE

Note: If you are unsure how dynamic queries work, we recommend reading our Dynamic Query article first.

 

1. Open/Create your View and run it. Then go into the Parameters.

mceclip0.png

 

 

2. Add parameters / edit a parameters Dynamic Query

mceclip3.png

 

 

3. Create Dynamic Query for the parameter and verify data is returned.

mceclip2.png

 

 

4. Modify the dynamic query using a WHERE clause. 

 

Example: SELECT DISTINCT EmployeeID FROM Orders WHERE EmployeeID IS NOT NULL AND EmployeeID >= 4

mceclip4.png

 

 

5. Verify the WHERE Clause is filtering out results by running the View. 

 

Example: The image on the left does not have a WHERE clause in the dynamic query and the image on the right uses the WHERE clause from step 4.

 

mceclip5.png

 

6. Set up FSGS to use the parameters in the filter statement and run the View.

mceclip6.png

 

 

Note: For an advanced technique that builds off this concept, try reading our article about cascading parameters.

Have more questions? Submit a request

Comments