Filtering Columns by Value

The WHERE section of the query editor allows you to filter the columns for specific values in a table. For example, instead of returning all the values in columns Student Name and Student GPA, you can use the WHERE section to specify only those values where the student has a GPA of 4.0. You can also apply operators and use wildcards for pattern searching.

To specify a value:

  1. Click Add.
  2. Select the name of the column in which the value is stored under Column.
  3. Specify the filtering condition by choosing an Operator and entering a Value. You can use an input parameter for the value.
  4. When you have more than WHERE condition, specify whether the conditions are related by AND or OR operators.

Input Parameters

You can specify the input value by entering a value directly. However, you may not know the specific value, or you may want to keep the query broad enough for multiple types of queries. Input parameters allow you to hold off on specifying a value until the query is actually being used. For example, you may want to specify the input value when you run the query within the editor or use it in the workflow activity.

To use an input parameter:

  1. Click the text under Input Value.

  2. Click Set as input parameter.

  3. Set the parameter name.
  4. Click OK.

Selecting Operators

The operator in a statement defines the relationship between a column and one or more values. Use the following operators to define the values you want to query. Use the NOT checkbox at the top of the drop-down to use the opposite of the selected option.

Operators
Name Description
IN A set of values. For example, "a" is in the set of values "a", "b", and "c".
BETWEEN A range of number or date values from the first value to the last.
LIKE Any value that fits a pattern in a column. Use wildcards to search for a pattern. (More information below)
COMPARISON A value based on a comparison between the column name and input value.
NULL Any columns that do not have a value.

Using AND or OR Operators between Different Rows

By default, if you have multiple rows in the WHERE section of the query editor, they will be connected by an AND operator. The query editor lets you change this to an OR operator, or to nest multiple levels of clauses linked by AND and OR operators.

  • To change an operator from an AND to an OR or vice versa, click on the operator and select your desired operator.
  • To join two rows with an AND or OR operator, drag one row onto the other row and select your desired operator.
  • To add a row into an AND or OR clause at the same level as another AND or OR clause, drag the row onto the latter AND or OR.

Example: I add three rows to a WHERE clause. Initially, the three rows are joined by AND operators by default. I want to move the row for the Discount condition to an OR condition grouped with CouponCode. In the animated image below, I accomplish this by dragging the Discount row to the CouponCode row and selecting OR.

Using Wildcards

Wildcards let you query values according to a pattern. You can use the following wildcard rules with a LIKE operator:

Operators
Name Description Example
Percent ("%") Represent zero or more characters.

"a%" queries values that start with "a"

"%a" queries values that end with "a".
Underscore ("_") Represent one character.

"_a" queries any two-letter values with "a" as the second letter

"_a%" queries any values with "a" as the second letter.