Creating Where Clauses

In SQL, conditions are called Where clauses. Where clauses reduce the number of rows to search. When you run reports, the data prints only if it meets all of the conditions defined in the Where clause.

A Where clause is essentially an equation that returns a value or values. Because it is an equation, it can be thought of in terms of a left side and a right side. In the example Where clause below, SAL is the left side of the equation, greater than is the operator, and 2500 is the right side of the equation.

WHERE SAL > 2500

*  To create a Where clause:

  1. Choose an option to place on the left side of the equation.

  2. Choose an operator or SQL predicate to join the two sides.

  3. Choose an option to place on the right side of the equation.

    Table 2. Valid Options for Where Clauses 

    Type of Clause (Left Side)

    Operator or SQL Predicate

    How to Evaluate the Value Selected (Right Side)

    Evaluate a Database Column or Expression

    Equal to

    Against a Database Column or Expression

    Evaluate a Constant or One-off Expression

    Not equal to

    Against a Constant or One-off Expression

    Evaluate a Value Prompted at Runtime (report parameter)

    Less than

    Against a Value Prompted at Runtime (report parameter)

    Test for Existence via a Subquery

    Greater than

    Against One or More Values from a Subquery

    Test for Non-Existence via a Subquery

    Less than or equal to

    		 
     

    Greater than or equal to

    		 
     

    IN

    		 
     

    NOT IN

    		 
     

    BETWEEN

    		 
     

    NOT BETWEEN

    		 
     

    LIKE

    		 
     

    NOT LIKE

    		 
     

    IS NULL

    		 
     

    IS NOT NULL

    		 

In the majority of instances, you can create a Where clause by taking an item from the first column of the table and using an operator or SQL predicate to join it to an item in the third column. However, there are limitations. For example, you cannot join two report parameters. The operator or SQL predicate used to join the two sides of the Where clause also determines what can go on the right side. Using IN for example, restricts the right side of the Where clause to a constant or one-off expression, a value prompted at runtime, or a subquery.

When you select an item on the left side of a Where clause and an operator or SQL predicate, SQR Production Reporting Studio disables any resulting invalid options for the right side of the Where clause.