Creating stored queries

Related Topics
Adding multiple variables to a stored query

About stored queries

A stored query is a predefined attribute query using SQL. Building stored queries saves your users the effort of creating their own query expressions. Author has a dialog box to help you create stored queries. Queries built in ArcIMS Author using this dialog box are accessed by your users on your Web site by clicking the Search button. The user then types a value and executes the search. A list of features matching the search criteria is returned.

When you create a query expression using the Stored Query tool, you are actually creating a Structured Query Language (SQL) statement. The following sections cover basic SQL. To build more complex expressions, refer to a SQL reference manual.

Searching for numeric values

To search for a specific value, use the = operator. To compare values, use the <, >, <=, >=, and <> operators. The query AGE=70 finds features that are exactly 70 years old, while the query AREA >= 1500 finds all features with an area greater than or equal to 1,500 square feet.

To perform calculations using numeric values from one or more columns, use the +, -, *, and / operators. For example, the expression NETVALUE=PRICE*AREA calculates NETVALUE by multiplying PRICE by AREA.

In longer SQL expressions, multiplication (*) and division (/) operators are evaluated first, from left to right, followed by the addition (+) and subtraction (-) operators. In the expression NETVALUE=TAX+PRICE*AREA, PRICE is multiplied by AREA first, then the result is added to TAX.

Searching for string values

When searching for a string, remember that string values are case sensitive and must be enclosed in single quotes. To search for the string 'California' in the STATE_NAME column, create the following expression: STATE_NAME='California'. This expression only finds features that have a STATE_NAME value exactly matching 'California'. The expression would not find features with 'california' or 'CALIFORNIA' as the state name.

Wild cards in string values

If you are not certain of the spelling of a word or want to use a shorter string in your expression, use the LIKE operator with the wild card operator '_', which represents a single character, or '%', which represents a group of characters.

For example, STATE_NAME LIKE 'Mi%' finds Michigan, Minnesota, Mississippi, and Missouri. OWNER_NAME LIKE '_erry' finds both Jerry and Gerry, but not Cherry because '_' only replaces one character.

Building expressions with more than one criteria

To build an expression with more than one criteria, use the AND, OR, and NOT operators.

When both criteria must be true, use the AND operator. For example, VEGETATION = 'Pine Forest' AND AREA > 100.
When only one of the criteria must be true, use the OR operator. For example, SALES > 20000 OR ORDERS > 20000.
To exclude a feature, use the NOT operator. For example, SUB_REGION = 'Pacific' AND NOT STATE_NAME = 'California'.

How to create stored queries

Creating a stored query

You create stored queries using the stored query dialog. You will first create a query using actual values. Once you are satisfied with the results, you can replace one of the values with a variable. For example, say you are setting up a query to find regions by ZIP Code. Your initial query expression looks like this: ZIP LIKE '94115'. Once you are sure the query is working, replace the value 94115 with '[%var%]'. Your expression now looks like the following: ZIP LIKE '[%var%]'.

Note that when you create stored queries using this dialog, you can include only one variable in your query expression.

Click a layer in the legend to make it active.
Click the Stored Query button on the Author toolbar.
A message box appears asking you if you want to create a Stored Query.
Click Yes.
A message box appears prompting you to enter a name for the Stored Query.
Type a name for the Stored Query and click OK.
In the Select a field list box, click a field.
If there are more than 100 different values for the selected field, you are asked if you want to display them all. Click Yes or No to dismiss the message box.
Click a query operator.
Click a value in the Values list box, or type a value in the expression text box.
Click Execute to find matching features.
Highlight the value in the expression box, and delete it.
Click the Insert Variable button to insert a variable where the value was.
Click the Save button.
Click OK to close the dialog box or the New button to create another query.

Search code: @author_stored_queries