Creating a query layer
You use the Query dialog box in ArcMap to create query layers. Before you can create a query layer, you must first make a connection to a database.
After a connection to the database is established, a list of tables and views found in that database will populate the left window of the dialog box. When you select one of the tables, the columns for that table are displayed in the right window.
Note:Each column in a database has a specific data type. ArcGIS can work with most common database types. However, some less common database types are not supported. If the attribute column type is Unknown, this indicates that ArcGIS does not support that data type. When you specify a query, all columns that have an unknown data type must either be excluded or changed to a different data type that ArcGIS supports.
To learn more about the data types that ArcGIS supports, see the following help topics:
A comparison of ArcGIS, ArcSDE, and DB2 data types
A comparison of ArcGIS, ArcSDE, and Informix data types
A comparison of ArcGIS, ArcSDE, and Oracle data types
- Specify a name in the Name text box for the query layer that will be created. This is the name that will appear in the ArcMap table of contents.
- Enter a SQL query into the Query text box.
-
Once the query is created, it must be validated. Click Validate to make sure the query syntax is correct and returns data that can be used by ArcGIS. The validation process executes the query in the database and verifies whether the result set from the query meets the data modeling standards enforced by ArcGIS. A query layer is not added to ArcMap until it is valid.
Rules for validation are as follows:
- The result set must have, at most, one spatial field.
- The result set must have, at most, one spatial reference.
- The result set must have only one entity type.
- The result set cannot have any field types not supported by ArcGIS.
If the validation fails for any reason, an error message is returned so you can modify the query.
Validation is especially important when working with data in spatial databases that do not enforce the same standards as ArcGIS.
Tip:During the validation process, ArcGIS sets the entity, spatial reference, and unique identifier properties on the query layer. These values are based on the first row returned in the query. If you need to change these settings, you can do so through the Advanced Options dialog box. Check the Show Advanced Properties check box to display the advanced options page in the new query layer dialog box.See the following topics for more information on the advanced options page: Choosing a unique identifier field and Defining the spatial reference of a query layer.
- If your query is successfully validated, click Finish to add the result set to ArcMap as a query layer.
When building a query, a whole table can be added to the Query text box by either double-clicking it or dragging it from the List of Tables window into the Query text box. Likewise, you can add specific columns in a table to the query by double-clicking them or dragging them from the Columns window into the Query text box. You can also type specific queries or cut and paste a query from an external application into the Query text box.
SQL syntax specific to the database should be used when building a query layer. A common example would be as follows: SELECT * FROM Test.dbo.US_States. This would result in a query layer containing all rows from the US_States table. In ArcMap, this would display all of the United States. For more information on building SQL queries, see About building a SQL expression.