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.
• > - the date is later than the specified date
• >= - the date is later than or same as the specified date
• < - the date is earlier than the specified date
• <= - the date is earlier than or same as the specified date
• between - the date is between (and including) the specified dates
• not between - the date is not between (and not including) the specified dates
• is null - the date is NULL (not specified)
• is not null - the date is not NULL (a date value is specified)

Numeric Operators

• = - the number is (numerically) equal to the specified number. Since this is a numerical comparison, there's no difference between "0.10" and ".1", for example
• ≠ - the number is not equal to the specified number
• between - the number is between (and including) the specified numbers
• not between - the number is not between (and not including) the specified numbers
• in - the number is equal to one of the specified numbers
• not in - the number is not equal to any of the specified numbers
• is null - the number is NULL (not specified). Note that a value of zero is not null
• is not null - the number is not NULL (a date value is specified)
• > - the number is higher than the specified number
• >= - the number is higher than or same as the specified number
• < - the number is lower than the specified number
• <= - the number is lower than or same as the specified number