Defining a SQL statement (Maritime Charting)

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.

Steps:

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

   The Layer Properties dialog box appears.

4. Click the Calculated Representations or Calculated Fields tab.
5. If necessary, create a new specification rule.
6. Click the SQL Statement drop-down arrow and click Edit SQL statement.

   The Query Builder dialog box appears.
7. Optionally, click Add Table to navigate to a feature class, relationship class, or table to add to the statement.
   Tip:

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

8. In the Fields list, choose a field you want to use in the selection or WHERE clause.
   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
9. Click Add to SELECT if you want to be able to use the field value to later define a VBScript.
10. Click Add to WHERE to use the field in your SQL statement.
11. Click one of the operator buttons.
12. Click All Values.

    All the unique values for the field appear in the Values list.

13. Choose one of the values in the Values list.
14. Click Add to WHERE.
15. Repeat steps 8–14 until your statement is complete.
    Tip:

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

16. Optionally, check the Edit query text check box.

    The SELECT, FROM, and WHERE text boxes become editable.

    Change the text in these text boxes as needed.

17. Click Verify to validate the SQL statement.
18. Optionally, click Save to save the SQL statement to a file.
19. Click OK.
    Tip:

    You can clear the contents of the SELECT OBJECTID and WHERE fields by clicking Clear.