Cart

Filter Data

Table of Contents

Filter Edit Rules

A WHERE cell can hold one of the following simple conditional expressions:

  • a string, numeric or datetime equality comparison, using the Equals or Not equals operator.
  • Is Null or Is Not Null special operators. = Null and <> Null automatically translate into Is Null or Is Not Null.
  • a string, numeric or datetime inequality comparison, using one of the <, <=, > or >= operators.
  • for strings, one of the [Not] Contains, Starts with or Ends with checks. (Note this translates into a SQL LIKE, but we kept it simple and removed all pattern specific complexities.)
  • check if value is [not] within a list of given element values.
  • check if value is [not] within all database values of a table column.

The WHERE clause is built as it follows:

  • join all non-empty simple conditional expressions from each horizontal line by the AND operator.
  • join all previous expressions on the vertical axe by the selected OR (default), AND, OR NOT or AND NOT operator.

First Example

Create a new query with the emp table, keeping only the fields below. Show Field and Where query builder’s rows. Turn paging off (click on Page Size). Click on Show Icons, to keep only used fields in the emp shape. Enter the simple conditional expressions, as they appear below, then run the query:

where-filter

Following the previous filter rules, our WHERE clause translates into:

WHERE (emp.ENAME NOT LIKE 'W%' AND emp.COMM IS NOT NULL)
	OR emp.JOB NOT IN ('SALESMAN', 'CLERK')
	AND NOT (emp.DEPTNO < 20 AND emp_.HIREDATE < '1982-12-12')
	OR NOT emp.ENAME <> 'TURNER'

You may temporarily disable the full WHERE clause by unchecking the check box. The row remains on screen (because it is not empty), but will be transparently omitted from your query and return all emp records. Go back with a simple click on the check box again.

Second Example

The following two queries return the same result and similar logical WHERE clause. This is because all expressions from the single row of the first queries are joined by AND following the first WHERE build rule. And the expressions of the second queries are join by AND because of the vertical specific operator for each row:

where-filter-2

where-filter-3

WHERE emp.ENAME NOT LIKE 'W%'
	AND emp.JOB IN ('SALESMAN', 'CLERK')
	AND emp.HIREDATE < '1982-12-12'
	AND emp.COMM IS NOT NULL

Third Example

The query below cannot be represented as such for (JOB = ‘SALESMAN’ OR JOB = ‘CLERK’) AND HIREDATE < ‘1982-12-12’. But (x OR y) AND z translates into (x AND z) OR (y AND z). So use it rather as (JOB = ‘SALESMAN’ AND HIREDATE < ‘1982-12-12’) OR (JOB = ‘CLERK’ AND HIREDATE < ‘1982-12-12’). Yes, you  may have to repeat some expressions in this case. Another possibility is to recognize you can use a In List Values operator and express all on a single row.

where-filter-4

Fourth Example

Slightly change the previous query for another common situation. Your row concatenation operator (below Where) is now AND, not OR:

where-filter-5

Detect some redundancy here, because (x AND y) AND (z AND y) may be translated into x AND y AND z. Your (JOB = ‘SALESMAN’ AND HIREDATE < ‘1982-12-12’) AND (JOB IS NOT NULL AND HIREDATE < ‘1982-12-12’) can become JOB = ‘SALESMAN’ AND HIREDATE < ‘1982-12-12’ AND JOB IS NOT NULL. You accomplish this by selecting a second (hidden) JOB field, but with a different condition, and getting rid of the second row:

where-filter-6

We introduced the Selection builder and unchecked the second JOB field because we do not need to show this data twice. In Design mode, no data is filled-in for that column. And in Results mode, it is hidden completely:

where-filter-7