Defining Criteria

To define criteria, use the Criteria and Or columns in Apply Operations. When writing conditions in these columns, omit the expression itself. For example, to get below criteria in your query:

WHERE  ("dbo"."testdata"."Age" >= 10) AND ("dbo"."testdata"."Age" <= 20)

Type as shown below in the Criteria cell of the "dbo"."testdata"."Age" expression:

>= 10 AND <= 20

Criteria placed in the Or column will be grouped by columns using the AND operator, then concatenated in the WHERE (or HAVING) clause using the OR operator. For example, the criteria shown below will produce the SQL statement below.

Note: The criteria for "dbo"."testdata"."Age" is placed both to the Criteria and Or columns.

Where ("dbo"."testdata"."Age" = 10 And "dbo"."testdata"."time" < 0) Or ("dbo"."testdata"."Age" = 10 And "dbo"."testdata"."time" > 10)

Some expressions may be of Boolean type, for example, the EXISTS clause. In this case, you should type "= True" in the Criteria column of such expressions or "= False" if you want to place a NOT operator before the expression.

Example of IN Operator

The IN operator specifies multiple values in a WHERE clause.

The SQL statement below selects all customers that are located in "Portugal", "Spain" or "Italy":

Select * from "dbo"."Customers"
WHERE Country IN ("Portugal", "Spain", "Italy");

The SQL statement below selects all customers that are from the same countries as the competitors:

Select * from "dbo"."Customers"
WHERE Country IN (Select "dbo"."Competitors"."Country" From "dbo"."Competitors");

Note: Ensure that the subquery, as shown in the above case, is fully qualified; if not, it may not work.

Example of BETWEEN Operator

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

The SQL statement below selects all customers with age BETWEEN 30 and 40:

Select * from "dbo"."Customers"
WHERE Age BETWEEN 30 AND 40;

The SQL statement below selects all customers with DateOfBirth BETWEEN '01-March-2020' and '30-April-2022':

Select * from "dbo"."Customers"
WHERE DateOfBirth BETWEEN #01/03/2020# AND #30/04/2022#;