Using Constant Values in WHERE Clauses

In many cases, it’s useful to constrain the result set to records with a particular ID value, constant name, or other value.  For example, to create a table showing only the Western Region’s orders, you may create selection criteria where one constraint is that the sales territory is “Western Region”, e.g.:

Order.SalesTerritory is equal to Western Region

Or, you might constrain the data to orders larger than $50,000, e.g.:

Order.TotalAmount is greater than 50000

 

Select Constant to constrain the selection criteria to a particular record ID value, constant name, or other value.

The Constant selection criteria is most frequently used with numerical and string values, though dates and other field types can be used as well.  This value is specified in string format and converted to the data type of the field selected on the left hand side.

You can combine Constant criteria with other selection criteria to create more refined data sets, e.g.:

Order.SalesTerritory is equal to Western Region AND

Order.TotalAmount is greater than 50000 AND

Order.SalesPersonID is equal to LoggedInUserID()

This example selects order data from the Western Region over $50,000 for the currently logged in sales person, giving the sales person a custom report showing only his or her large orders.

Using ‘Display Foreign Key As’ values as constants

You can enter foreign key values as constants in addition to numeric values.  However, certain caveats apply:

See Primary and Foreign Key Options for details.

See Also

Customizing Database Queries with the Query Wizard

Using Tables and Views in WHERE Clauses

Using Constant Values in WHERE Clauses

Using URL Parameters in WHERE Clauses

Using the Logged-In User ID in WHERE Clauses

Calling Custom Code Functions in WHERE Clauses

Adding a Custom Query