When you create a data export, you may need to filter the results based on certain conditions. This is where SQL operators come into play.
To filter the results, enter an operator followed by a value into the Criteria field for the relevant data.
Equals (=)
To filter rows where a column is equal to a given value, use the = operator. For example, to filter by employees whose effective status is current, in the Criteria column for effective status, enter = 'current'.
🤓 Tip : String values, such as nvarchar, require quotation marks.
Greater than or equal to and less than or equal to
To filter rows when a column is greater than or equal to, or less than or equal to a value, use the >= or <= operators. For example, to filter by holiday rules with more than 21 days of entitlement, in the Criteria column for holiday rule, enter >=21.
IN operator and OR conditions
To specify multiple values in a WHERE clause, you can use either the IN operator or multiple OR conditions. In the example, For example, to filter by employees with person numbers 15 and 18, you can either method.
Note: Regardless of which method you use, the result is the same. However, if you have lots of values to enter, using the IN operator is more efficient.
Multiple OR conditions
For the example above, in the Criteria column for person number, enter = 15. Then, in the Or... column, enter = 18. The SQL code for this option is:
SELECT Person.Snapshot.[Person Number],Person.Snapshot.[First Name],Person.Snapshot.SurnameFROM Person.SnapshotWHERE (Person.Snapshot.[Person Number] = 15)OR (Person.Snapshot.[Person Number] = 18)
IN operator
For the example above, in the Criteria column for person number, enter IN (15, 18). The SQL code for this option is:
SELECT Person.Snapshot.[Person Number]
,Person.Snapshot.[First Name]
,Person.Snapshot.Surname
FROM Person.Snapshot
WHERE Person.Snapshot.[Person Number] IN (15, 18)
LIKE operator
To search for a specific pattern in a column, use the LIKE operator. The % wildcard represents zero or more characters. For example, to filter by employees whose first names start with J, in the Crieria column for first name, enter Like 'J%'.