SQL Operators

Use a number of operators, such as "LIKE," "IN," and multiple comparative operators when you perform a SQL Where Clause lookup.

When you combine fields from the same grid where some of the operators are "not" type operators, the SQL Where Clause will only be built correctly when the "not" type operators follow the non "not" type operators.

Operator

Description

Comparative

Operator	Meaning
=	Is equal to
NOT=	Is not equal to
>=	Is greater than or equal to
<=	Is less than or equal to
<	Is less than
>	Is greater than

Comparative operators are used in WHERE clauses to select projects based on numeric or character values. For example, the following WHERE clause, in which the comparative operator is >, searches for all projects where the project number is greater than 97000.00:

( PROJ.prProject>’97000.00’)

WHERE clauses are not case-sensitive. For example, you can enter APPLE or apple or Apple as a value, and the WHERE clause will find any possible upper- or lower-case combination of that name.

You can use the following comparative operators in a WHERE clause:

LIKE

LIKE compares a string of characters.

When you use the LIKE operator, you can use wildcard characters rather than enter the entire value.

Vision automatically adds a % to the end of every character string.

For the LIKE operator, you can use the % and _ (underscore) wildcard characters.

% retrieves a string of zero or more characters of any value. (The % wildcard is similar to the * wildcard in DOS.)
_ retrieves a single character of any value. (The _ is similar to the ? wildcard in DOS.)

For example, if you want projects and tasks for all client names ending in CH, you can enter % CH in the Value field. The WHERE clause retrieves all projects whose client name ends with CH.

In the following WHERE clause, LIKE ’r; apple%’ searches for all projects where the project manager’s name starts with the letters Apple:

( PROJ.prProjMgr LIKE ’r; apple%’)

Use the IN operator when you are looking for information contained in a group of values.

For example, if you are searching for only overhead and promotional jobs (types H and P), you can use the syntax:

( PROJ.prType IN H,P)

instead of:

( PROJ.prType=’H’) OR ( PROJpr.Type=’P’)

AND OR

The logical operators AND and OR combine multiple conditions in a WHERE clause.

For example, you can query for all projects WHERE the project manager is LIKE Apple AND the client number is 1234:

( PROJ.prProjMgr LIKE ’r; APPLE%’)

AND ( PROJ.prClient=’1234’)

This clause retrieves all projects for client 1234 for which Apple is the project manager.

The following WHERE clause retrieves all tasks for projects that have Apple as the manager of the project, and any other specific tasks that have Apple as the manager of the task:

( PROJ.prProjMgr LIKE ’r; APPLE%’)

OR ( TASK.prProjMgr LIKE ’r; APPLE%’)