Add Query Criteria

When you want to limit the results of a query based on the values in a field, you use query criteria. A query criterion is an expression that ClicknDECiDE Builder compares to query field values to determine whether to include the record that contains each value. For example, = "London" is an expression that ClicknDECiDE Vision can compare to values in a text field in a query. If the value for that field in a given record is "London", ClicknDECiDE Builder includes the record in the query results.

This topic lists several examples of query criteria. It assumes that you are familiar with designing simple select queries.

What do you want to do?

  Introduction to query criteria

 Add query criteria

 User query criteria symbols

 Open the ClicknDECiDE SQL Editor

 Open the Table tab

Introduction to Query Criteria

A criterion is similar to a formula — it is a string that may consist of field references, operators (operator: a sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.), and constants (constant: a value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.).

The following tables shows some sample criteria and explains how they work. Criteria Description

As you can see, criteria can look very different from each other, depending on the data type of the field to which they apply and your specific requirements. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references.

This topic lists several commonly used criteria by data type. If the examples given in this topic do not address your specific needs, you might need to write your own criteria. To do that, you must first familiarize yourself with the full list of functions, operators, special characters, and the syntax for expressions referring to fields and literals. For more information, see the articles listed in the See also section.

Here, you will see where and how you add the criteria.

 Top of Page 

Add Query Criteria

To add query criteria, perform the steps below:

Note: it is not necessary to select the field you want to specify criteria for. You can add criteria even if the field is not selected.
 

1. Click the blue button in the Criteria column to the left of the field which you want to specify criteria for. In this example the DATE field. The Criteria dialog box appears.
   
   
   
   
    

2. Select the Columns drop-down list for the required field displayed in the format TABLE_NAME.COLUMN_NAME, in this example, Sales.DATE (for the Sales table Date column). The default field displayed is the field selected when you clicked a blue button.
   
   
    

3. Select the criteria condition n the Operators drop-down list.

4. Select one of the four tabs:Value, Column, Expression or Query which corresponds to the criteria type you want to apply.

5. Note the Where tab automatically displays the SQL syntax of the criteria specified.
   Where tab: concerns all the criteria applied to direct column names.
   Having tab: concerns all the criteria applied to column names used in a Group function such as SUM, MIN, MAX, AVG and so on.
   
   

6. To add criteria, select the appropriate column, operators and criteria type and click New. The new criteria will be displayed in the Where tab.
   
   
    

7. Switch between the and/or statements by clicking and/or.

8. Apply a Not statement by selecting the appropriate row and clicking Not. To remove the Not statement, click Not again.

9. Combine several criteria by selecting each row and holding down CTRL, then click Combine parentheses will surround the rows selected. To uncombine criteria, select the appropriate opening parenthesis, then click Uncombine. The Combine will be removed.
   
   

 Top of Page