Using a WHERE Clause in Dynamic Queries for Parameters

Trouble Viewing Images? Right-click on any image and select "Open in new tab" to view a larger version. You can also zoom in using Ctrl + Mouse Wheel for easier readability.

Article Goal

Show how to filter values returned by dynamic queries for parameters using a WHERE clause, helping to reduce unnecessary options and focus on relevant results.

What Is a WHERE Clause?

A WHERE clause in SQL is used to filter rows returned by a query. It allows you to specify criteria that must be met, making your result set more precise and useful.

Tip: Adding a WHERE clause helps keep parameter lists clean by excluding NULL values or applying business rules like minimum or maximum limits.

Why Use a WHERE Clause in Dynamic Queries?

• Reduce the number of returned values

• Prevent NULL or unwanted data from appearing in parameter lists

• Improve parameter usability and relevance

• Enhance performance for large datasets

Use Case: You only want to allow employees with an ID of 4 or higher to appear in a dropdown. Use a WHERE EmployeeID >= 4 clause to enforce that condition.

Download Sample View:
Click here to download the sample view used in this demonstration.

Steps to Create a Dynamic Query with a WHERE Clause

Step 1: Open or Create a View

• Launch VDM

• Create or open a View that uses parameters

• Click Run View to initialize

Step 2: Access the Parameters Setup

• Click the Parameters button to open the list

• Add a new parameter or edit an existing one

Step 3: Set Dynamic to True

• Toggle the Dynamic setting to True

• This enables SQL-based population of the parameter values

Step 4: Enter the Base Dynamic Query

Start with a basic query that returns values:

Ensure it works by checking that values are returned when running the View.

Step 5: Modify the Query to Include a WHERE Clause

Add a WHERE clause to filter values:

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

Step 6: Run the View and Confirm Results

• Run the View again

• Confirm the parameter now only shows filtered results according to your WHERE clause logic

Example Comparison:

• Left image: No WHERE clause – shows all values

• Right image: With WHERE clause – shows only filtered values

Step 7: Use the Parameter in FSGS

• Go to the FSGS tab under Query Builder

• Use the parameter you just configured in a filter statement

• Run the View to validate both the parameter and filtering

Article Summary

Dynamic Queries in VDM can be enhanced using SQL WHERE clauses to control which values are presented to users in parameters. This improves usability, relevance, and consistency, especially when working with large or inconsistent datasets. Whether you're excluding nulls or setting thresholds, filtering at the SQL level simplifies parameter management.

Use Case: You want to populate a dropdown for Product Categories, but only for active items. A dynamic query using WHERE IsActive = 1 ensures the list is always accurate.