Make Query Table (Data Management) |
|
Release 9.2
Last modified November 29, 2010 |
![]() ![]() ![]() Print all topics in : "Tools" |
The Make Query Table tool applies an SQL query to a database and the results are represented in a layer or table view. The query can be used to join several tables or return a subset of columns or rows from the original data in the database.
Make Query Table accepts data from an ArcSDE geodatabase, a file geodatabase, a personal geodatabase, or an OLE DB connection.
Usage tips
All input feature classes or tables must be from the same input workspace.
If a Shape column is added to the field list, the result is a layer; otherwise, it is a table view.
If the output result is a layer, it may be persisted to a layer file using the Save To Layer File tool or to a feature class using the Copy Features tool.
The order of the fields in the field list indicates the order the fields will appear in the output layer or table view.
The tool allows you to provide a key field option and key fields list. This information defines how rows are uniquely identified and is used to add a dynamically generated ObjectID column to the data. Without an ObjectID column, selections will not be supported.
The key fields list lets you choose several columns if the combination of these columns is needed to define unique values.
If an SQL expression is used but returns nothing, the output feature class will be empty.
Feature classes can be joined, but the fields list must contain at most one field of type geometry. If you add more than one geometry column, the tool will error when you click OK.
For details on the syntax for the Expression parameter, see Building an SQL Expression or SQL Reference.
The Fields and Key Fields parameters' Add Field button is used only in ModelBuilder. In ModelBuilder, where the preceding tool has not been run, or its derived data does not exist, the Fields and Key Fields parameters may not be populated with field names. The Add Field button allows you to add expected field(s) so you can complete the Make Query Table dialog and continue to build your model.
When input tables are from a file geodatabase, tables generally join in the order listed in the Input Tables parameter. For example, if Table1 is listed before Table2, Table2 will be joined by getting a row from Table1, then getting matching rows from Table2. However, if this would result in querying Table2 on an un-indexed field, and reversing the order would result in querying Table1 on a indexed field, the order will be reversed in an attempt to maximize performance. This is the sole query optimization logic at work when you're using file geodatabase data with this tool. In general, joins in file geodatabases perform best when they are one-to-many and one-to-one.
The following environment settings affect this tool: workspace, scratch workspace, Extent, M Domain, Configuration keyword, Coordinate system, Output has M values, Output spatial grid, Output has Z values, Default Z value, Output XY domain, and Output Z domain.
Command line syntax
An overview of the Command Line window
MakeQueryTable_management <in_table;in_table...> <out_table> <USE KEY FIELDS | ADD VIRTUAL KEY FIELD | NO KEY FIELD> {in_key_field;in_key_field...} <Field {Alias};Field {Alias}...> {where_clause}
Parameter | Explanation | Data Type |
<in_table;in_table...> |
The name of the table or tables to be used in the query. If several tables are listed, the Expression parameter can be used to define how they are to be joined. The input table can be from an ArcSDE geodatabase, a file geodatabase, a personal geodatabase or an OLE DB connection. |
Table View | Raster Layer |
<out_table> |
The name of the layer or table view that will be created by the tool. |
Table View | Raster Layer |
<USE KEY FIELDS | ADD VIRTUAL KEY FIELD | NO KEY FIELD> |
Indicates how an ObjectID field will be generated, if at all, for the query. The default is USE_KEY_FIELDS.
|
String |
{in_key_field;in_key_field...} |
Specifies a field or combination of fields that can be used to uniquely identify a row in the query. This parameter is used only when the USE_KEY_FIELDS option is set. The Add Field button, which is used only in ModelBuilder, allows you to add expected field(s) so you can complete the dialog and continue to build your model. |
Field |
<Field {Alias};Field {Alias}...> |
The fields to include in the layer or table view. If an alias is set for a field, this is the name that appears. If no fields are specified, all fields from all tables are included. The Add Field button, which is used only in ModelBuilder, allows you to add expected field(s) so you can complete the dialog and continue to build your model. |
String |
{where_clause} |
An SQL expression used to select a subset of records. The syntax for the expression differs slightly depending on the data source. For example, if you're querying file or ArcSDE geodatabases, shapefiles, coverages, dBASE or INFO tables, enclose field names in double quotes: "MY_FIELD" If you're querying personal geodatabases, enclose fields in square brackets: [MY_FIELD]. For more information on SQL syntax and how it differs between data sources, see SQL Reference. |
SQL Expression |
MakeQueryTable "Database Connections/balrog.odc/vtest.COUNTIES;Database Connections/balrog.odc/vtest.CODEMOG" vtest.COUNTIES.OBJECTID 'ObjectID' ;vtest.COUNTIES.NAME 'Name';vtest.CODEMOG.Males 'Males';vtest.CODEMOG.Females 'Females'" "vtest.COUNTIES.FIPS = vtest.CODEMOG.Fips and vtest.COUNTIES.STATE_NAME = 'California'"
Scripting syntax
About getting started with writing geoprocessing scripts
MakeQueryTable_management (in_table, out_table, in_key_field_option, in_key_field, in_field, where_clause)
Parameter | Explanation | Data Type |
in_table (Required) |
The name of the table or tables to be used in the query. If several tables are listed, the Expression parameter can be used to define how they are to be joined. The input table can be from an ArcSDE geodatabase, a file geodatabase, a personal geodatabase or an OLE DB connection. |
Table View | Raster Layer |
out_table (Required) |
The name of the layer or table view that will be created by the tool. |
Table View | Raster Layer |
in_key_field_option (Required) |
Indicates how an ObjectID field will be generated, if at all, for the query. The default is USE_KEY_FIELDS.
|
String |
in_key_field (Optional) |
Specifies a field or combination of fields that can be used to uniquely identify a row in the query. This parameter is used only when the USE_KEY_FIELDS option is set. The Add Field button, which is used only in ModelBuilder, allows you to add expected field(s) so you can complete the dialog and continue to build your model. |
Field |
in_field (Required) |
The fields to include in the layer or table view. If an alias is set for a field, this is the name that appears. If no fields are specified, all fields from all tables are included. The Add Field button, which is used only in ModelBuilder, allows you to add expected field(s) so you can complete the dialog and continue to build your model. |
String |
where_clause (Optional) |
An SQL expression used to select a subset of records. The syntax for the expression differs slightly depending on the data source. For example, if you're querying file or ArcSDE geodatabases, shapefiles, coverages, dBASE or INFO tables, enclose field names in double quotes: "MY_FIELD" If you're querying personal geodatabases, enclose fields in square brackets: [MY_FIELD]. For more information on SQL syntax and how it differs between data sources, see SQL Reference. |
SQL Expression |
# MakeQueryTableOLEDB.py # Description: Create a query table from two OLE DB tables using a limited set of # fields and establishing a join. # Author: ESRI # Date 1/30/2004 # Create the geoprocessor object import arcgisscripting gp = arcgisscripting.create() try: # Local variables... Table_list = "Database Connections/balrog.odc/vtest.COUNTIES; Database Connections/balrog.odc/vtest.CODEMOG" Field_list = "vtest.COUNTIES.OBJECTID 'ObjectID' ;vtest.COUNTIES.NAME 'Name';vtest.CODEMOG.Males 'Males';vtest.CODEMOG.Females 'Females'" where_clause = "vtest.COUNTIES.FIPS = vtest.CODEMOG.Fips and vtest.COUNTIES.STATE_NAME = 'California'" # Make Query Table... gp.MakeQueryTable(Table_list,"CountyCombined","USE_KEY_FIELDS","vtest.COUNTIES.OBJECTID",field_list,where_clause) # Print the total rows pDSC = gp.describe("CountyCombined") print gp.getcount("CountyCombined") # Print the fields Fields = pDSC.Fields Field = Fields.Next() while Field: print Field.Name fld = Fields.Next() # Save as a dBASE file gp.CopyRows("CountyCombined", "d:/temp/calinfo.dbf") except: # If an error occurred print the message to the screen print gp.GetMessages()