Use this type of Where clause to evaluate a specific database column or expression. The Where clause limits the report to the data that meets the condition defined by the Where clause.
For example, you could create a report that limits the data printed to customers who had orders greater than $1000.
In this example, we will select Evaluate a Database Column or Expression on the left side of the Where clause and Against a Constant or One-off Expression on the right side of the Where Clause.
To create a Where clause to evaluate a database column or expression:
Click Add Clause in the main Where Clauses dialog box.
Select Evaluate a Database Column or Expression as the type of clause.
Select the database column or expression to evaluate.
SQR Production Reporting Studio places the database column or expression on the left side of the Where clause equation.
For example, to limit the report to orders greater than a certain amount, you would choose Price as the database column.
Choose the comparison operator or SQL predicate to use in the clause.
SQR Production Reporting Studio uses this operator or SQL predicate to evaluate the selected database column or expression.
For example, to limit the report to orders greater than $1000, you would choose Greater than as the comparison operator.
You can modify comparison operators with the ANY or ALL modifiers. Comparison modifiers are mutually exclusive – you can select ANY, ALL, or None, but not all three. If you choose ANY or ALL, the right side of the Where clause must be a subquery, and the appropriate dialog boxes will appear. (See Creating a Where Clause Using ANY or ALL for an example.)
When you evaluate a text column or an expression, you can also the SQL predicates LIKE and NOT LIKE. The LIKE predicate supports the use of wildcard characters where comparing text strings. For example, PRODUCT.DESCRIPTION LIKE %LIGHT% returns all products descriptions containing the word “light” (light bulbs, desk light).
If you are creating a list, use IN or NOT IN. If you choose IS NULL or IS NOT NULL, the Where clause does not have a right side.
Choose how to evaluate the database column or expression selected.
Depending on the operator or SQL predicate selected, you can evaluate the database column or expression against another column or expression, against a constant or a one-off expression, against a value prompted at runtime, or against one or more values from a subquery.
In our example, we want to evaluate the Price column against the constant value $1000. So, we would select Against a Constant or one-off Expression as the option to place on the right side of the Where clause.
Enter the constant against which to evaluate the column or expression.
In our example, we want to evaluate the Price column against the constant value $1000. So, we would enter 1000 as the constant.
If you chose to evaluate the database column against another database column or expression, against a value prompted at runtime, or against one or more values from a subquery, the appropriate dialog boxes would appear instead of the constant or SQL expression dialog box above.
Verify that the Where clause is correct and click Finish to return to the main Where Clauses dialog box.
In our example, the Where clause to limit the report to customers who had orders greater than $1000 would appear in the main Where Clauses dialog box as follows:
PRODUCTS.PRICE>1000