Adding Record Selection Conditions

Record selection condition expressions indicate which records from the underlying tables are chosen during query execution. In essence, an expression is evaluated as either true, false, or unknown for each record retrieved by the data manager. If it evaluates to a true condition, the record is accepted and returned. Otherwise, the record is rejected and another is retrieved from the database.

The processes below are the same for new queries, and for modifying existing queries (once opened in the editor).

Create an Expression

Steps

1. Once in the Query Editor (see Creating a New Query for information) press the Conditions band.

   The band will expand to display the expression builder.

   

2. Hover your mouse on the Add Element button to view the available elements that make up and can be added to the condition expression.

   

   Select one of the following:

   Condition - Choose from any of the standard or boolean conditions (AND, OR, =, <, >, and so on).

   Column - Select a column from any of the tables used by the query.

   Value - Enter a numeric or character value for the expression like, filename.state = "IL".

   Function - Aggregate functions such as SUM, AVG, MAX, MIN applied to a column can be part of a Where Condition.

   Symbol - Use this to create expressions by adding the following symbols: ( ) , + - / * %.

   Parameter - Add any parameters defined in the query (see below).

   Each added element will provide you with an entry box—many with drop-down lists—in order to select different items for the element, to enter values, or insert parentheses. At the same time, a new Add Element button displays so you can continue and add to the expression.

   

   Most expressions are very simple and follow the form:

   COL- COND - COL for example: table.price > table.cost

   COL - COND - VAL for example: table.price = 100

3. Continue adding elements to complete the condition.
4. Press Save when finished.

Conditions for Grouped Results

Just as with detail queries, grouped queries can use expressions to filter the records returned. The HAVING condition is similar to the condition created above (for the WHERE clause), but it applies to the grouped records rather than the underlying "un-grouped" or detail records.

The tests allowed in the HAVING condition are the same as those allowed above with the addition of one more for aggregate functions.

Steps

1. Press the Having band.

   The band will expand to display the expression builder.

   

2. Hover your mouse on the Add Element button to view the available elements that make up and can be added to the condition expression.

   

   Select one of the following:

   Condition - Choose from any of the standard or boolean conditions (AND, OR, =, <, >, and so on).

   Column - Select a column from any of the tables used by the query.

   Value - Enter a numeric or character value for the expression like, filename.state = "IL".

   Function - Aggregate functions such as SUM, AVG, MAX, MIN applied to a column can be part of a Where Condition.

   Symbol - Use this to create expressions by adding the following symbols: ( ) , + - / * %.

   Parameter - Add any parameters defined in the query (see below).

   Each added element will provide you with an entry box—many with drop-down lists—in order to select different items for the element, to enter values, or insert parentheses. At the same time, a new Add Element button displays so you can continue and add to the expression.

   

3. Continue adding elements to complete the condition.
4. Press Save when finished.

Conditions and Parameters

Parameters are a powerful addition to conditions and record selection. Parameters allow for user input when interacting with the query or the dashboard a query is added to. Once a parameter is created and defined, it can be used in conditions.

The steps to add a parameter are the same as described above for the WHERE and HAVING clauses above.

Select Parameter.

Use the drop-down to select any defined parameters.

 

Related Topics