Joining Where Clauses

Joining Where Clauses involves:

Using the Logical Operators AND/OR/ELSE

When you have several Where clauses, you can join them with the logical operators AND or OR. When you join more than one Where clause that evaluates a value prompted at runtime (report parameter), you can also use the logical operator ELSE. Select the operator you wish to use in the Logical Operator list box found in the Where Clauses dialog box.

As an example, assume you create two Where clauses:

The first Where clause limits the report to customers with a specific Customer Number.
The second Where clause limits the report to customers with a specific Name.

When you run the report, SQR Production Reporting Studio behaves differently depending on the type of join you used to combine the Where clauses.

AND

AND joins two or more conditions and returns a row only if all of the conditions are true for that row.

In this example, if you use AND as the Logical Operator and run the report:

A prompt to enter a value for Customer Number appears.
If you do not enter a value, SQR Production Reporting Studio stops running the report, defaults to the value specified for the Where clause, or repeats the prompt until you enter a value.
After you enter a value for Customer Number, a prompt to enter a value for Customer Name appears.
When SQR Production Reporting Studio runs the report, it looks for a value in response to both prompts and displays those records that match both the Customer Number and the Customer Name.

OR joins two or more conditions and returns a row if any of the conditions are true for that row.

In this example, if you use OR as the Logical Operator and run the report:

A prompt to enter a value for Customer Number appears.
If you do not enter a value SQR Production Reporting Studio stops running the report, defaults to the value specified in the Where clause, or repeats the prompt until you enter a value.
After you enter a value for Customer Number, a prompt to enter a value for Customer Name appears.
When SQR Production Reporting Studio runs the report, it looks for a value in response to both prompts and displays those records that match either the Customer Number or the Customer Name.

ELSE

ELSE joins two or more conditions, but only evaluates one of them.

In this example, if you use ELSE as the Logical Operator and run the report, a prompt to enter a value for Customer Number appears.

If you enter a value, SQR Production Reporting Studio skips the second prompt and displays the records that match the Customer Number entered.
If you do not enter a value, SQR Production Reporting Studio ignores the first prompt and displays a prompt for you to enter the value for Customer Name.

When SQR Production Reporting Studio runs the report, it looks for a value in response to the first prompt. If it finds a value, it skips the second prompt and displays the records that match the value in the first prompt (in this example, Customer Number). If you do not enter a value in the first prompt, SQR Production Reporting Studio displays the second prompt and displays the matching records (in this example, Customer Name).

Specifying Order Precedence

When you use AND, OR, and ELSE operators to form complex expressions, you can use the parenthesis buttons to specify order precedence.

To add a parenthesis above a line:

Select the line above which you want to add the parenthesis and click the open parenthesis button.

To add a parenthesis below a line:

Select the line below which you want to add the parenthesis and click the close parenthesis button.

After you add the parentheses, click OK. SQR Production Reporting Studio validates the Where clause. If the validation fails, an error appears.

To remove a parenthesis:

Select the parenthesis and click Remove. Only the parenthesis associated with the clause is deleted. Matching open or close parentheses are not deleted.