Defining a Filter

A filter consists of one ore more conditions combined with "and" and "or". The filter restricts the rows to be included in the view to only those rows that meet the filter conditions. For example, in an Employee table, a filter such as "gender = M and job_title = Clerk" will only include table rows where the gender is "M" (M is the data value for a male) and the job title is clerk.

Each condition in the filter consists of a field name, and operator (e.g. the equals operator, "="), and optionally some operands. Let's see a few examples.

description is empty

In this example, the condition says that the description field has an empty value (it is either NULL or blank). In this example, the operator is "is empty" and no operands.

name between a and k

In this example, the condition says that the name field has a value that is greater or equals to "a" (in dictionary order) and less than or equals to "k". In this example, the operator is "between" and there are two operands.

The list of operators that are available for each field depends on its type.

Date/Time Operators

For a datetime field, the following operators are available

• on - the date is on the specified date - any time during the given date
• not on - the date is not on the specified date - any date but the specified date
• during - the date is during the specified period which is relative to the curent date. This includes "today", "yesterday", "this week" (starting Sunday), "this month", "this quarter", "this year", "last week", "last month", "last quarter", and "last year"
• not during - the date is not during the specified period (see during)
• before - the date is before (and not including) the specified period
• after - the date is after (and not including) the specified period
• during last - the date/time is during the last specified number of hours/days/weeks/months/years. Here "days" simply mean periods of 24 hours. "weeks" means periods of 7 days, and so on.