This section describes the main operators that are used in the SQL Where Clause search. When combining fields from the same grid where some of the operators are "not" type operators, Deltek PM Compass only builds the SQL Where Clause correctly when the "not" type operators follow the non "not" type operators.
For more information on search types and options, see Search Overview.
You use comparative operators 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 that 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 finds any possible upper- or lower-case combination of that name.
You can use the following comparative operators when creating a Where clause:
|
Operator |
Description |
|
= |
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 |
LIKE compares a string of characters. When you use the LIKE operator, you can use wildcard characters rather than enter the entire value. 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.
To retrieve all projects whose project manager name contains the string AN, enter %AN for the value. PM Compass 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.
_ retrieves a single character of any value.
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%’)
IN is equal to any of a series of values.
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’)
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%')
Perform an SQL Where Clause search
Select multiple records from a search dialog box