Defining a SQL statement

To create calculated representation rules, you define a set of features to symbolize. To create calculated field rules, you define a set of features to which you are going to add fields. You define sets of features by creating a SQL SELECT statement.

A SQL SELECT statement retrieves features or rows from one or more tables in a database. These statements use field-operator-value combinations, known as predicates, in a SQL WHERE clause to specify which features or rows to retrieve. For example, a SQL statement to find streets edited by Sam would look like the following:

SELECT NAME FROM Streets WHERE EDITOR='Sam' 

Two or more tables are related if each has a field that contains the same data as the other. You use these relationships to access information located in another table. To access this related information, you join the tables, based on a common field, in a SQL SELECT statement. You can also use a join to limit a SELECT statement to only those rows that exist in both tables.

For example, to select BuoyP features that exist in both the BuoyP feature class and the PRODMAP_FREL table, you would configure a statement similar to the following:

SELECT OBJECTID FROM BuoyP, PRODMAP_FREL WHERE LNAM=PRODMAP_FREL.SRCNAM 

In this example, the BuoyP LNAM field and the PRODMAP_FREL SRCNAM field share some or all values. The SELECT statement will return OBJECTIDs from BuoyP where values in LNAME and SRCNAM match.

The Visual Specifications Query Builder allows you to build SQL SELECT statements. These statements can include one or more related feature classes or tables. You can use the Visual Specifications Query Builder dialog box to define and save statements. You can also type SQL statements in some of the controls on the Query Builder dialog box.

1. Start ArcMap .
2. Load data in the map if necessary.
3. Right-click the layer for which you want to define a calculated specification rule and click Properties .

The Query Builder dialog box appears.

Tip:

To add multiple feature classes and tables, hold the Ctrl key down while clicking items in the Add Table dialog box.

Note:

If you added a table or feature class in step 7, scroll down the Fields list to see the added table and its fields. If your Fields list contains identical field names from different tables or feature classes, prefix each field name with its table name and assign an alias to it. BuildingP.Name as BuildPName, BuildingA.Name as BuildAName

Tip:

Separate each WHERE clause field-operator-value combination (a SQL predicate) with Like , And , Or , Is , % (modulo), or Not .

The SELECT , FROM , and WHERE text boxes become editable. Change the text in these text boxes as needed.