You are here: Reference information > Building WHERE clauses

Building WHERE clauses

Spatial Query Web Service and Data Manager Web Service allow you to define your search criteria and to focus the results using a type of query expression called a WHERE clause.

WHERE clauses allow use to define one or more criteria that consist of attributes, Structured Query Language (SQL)operators, and calculations. For example, imagine you have a map of customers and want to find those who spent more than $50,000 with you last year and whose business type is "Restaurant". You would select the customers with this WHERE clause: Sales > 50000 AND Business_type = 'Restaurant'.

Spatial Query Web Service recognizes the following SQL operators: =, AND, OR, >, >=, <, <=, <>, BETWEEN, IN, LIKE, _, %, and NOT. The following examples demonstrate the various formats you can use to create your WHERE clause.

Use a basic WHERE clause.

Example: City='Murrieta'

Result: locations in Murrieta

Put more than one clause together.

'AND' finds records that meet both criteria.

Example: Type='Amusement Parks' AND State='CA'

Result: Amusement Parks in CA.

'OR' finds records that meet one or both criteria.

Example: Type='Amusement Parks' OR State='CA'

Result: Amusement Parks located anywhere and all places within CA.

Use different types of comparison operators. These can be used with letters as well as numbers.

> is strictly greater than

Example: Name>'D'

Result: Names beginning E-Z and characters like ~ and -

<= is less than or equal to

Example: Zone<='60000'

Result: Zones 60000 and smaller.

<> is not equal to

Example: State<>'CA'

Result: all states that are not CA.

Use extended comparisons.

'BETWEEN' tests whether a value is between two other values.

Example: City BETWEEN 'E' AND 'L'

Result: Cities such as Fedora that fall between E and L alphabetically.

'IN' tests whether a value is in a list of possible values.

Example: Type IN ('red', 'blue', 'green')

Result: All Types are that either red, blue or green.

Use 'LIKE' for pattern matching.

_ tests for exactly one wildcard character.

Example: State LIKE 'W_'

Result: States that begin with W, such as WA, WI, WV, and WY.

% tests for zero to multiple wildcards.

Example: City LIKE '%ing'

Result: Cities that end in 'ing' such as Redding.

Use 'NOT' for negating a clause.

Example: NOT Type='red'

Result: Types that are not red.

Use this format when the data type of the field is date time (including UNIX time).

Example: Reporttime > '21-APR-05' and TIMESTAMP < '22-APR-05'

Result: Times between April 21 and April 22, 2005

Example: Reporttime > TO_DATE('04/10/2005, 11:03 A.M.', 'MM/DD/YYYY, HH:MI A.M.')

Result: Times later than April 10, 2003, 11:03 a.m.

Visit the Feedback page to give comments or suggestions about the ArcWeb Developer's Guide.

ArcWeb site | ArcWeb support | support.esri.com