English

How To: Properly format an SQL expression to make selections in joined layers and tables

Summary

The Select by Attributes dialog box in ArcMap requires an SQL expression to make a selection. The source of the queried data determines the version of SQL to be used. Here are the data sources and their associated SQL versions:

ArcSDE Geodatabase data - ANSI SQL
Personal Geodatabase data - JET SQL
File based data (shapefiles, coverages, etc.) - limited version of SQL
OLE DB data - Native SQL for the database

For more information on these types of SQL, refer to Building a query expression. Also click the Help button in the Select by Attributes dialog box.

When building SQL expressions for joined layers and tables, the SQL version used depends on the source of the joined data and the query expression.

Procedure

The following identifies rules to follow when building the SQL statement:

  • If all of the data involved in the join has the same data source, use the SQL version for that source. For example, if a personal geodatabase table is being joined to a personal geodatabase feature class, use JET SQL.
  • If the data involved in the join has different data sources, the query expression determines the SQL version. The following outlines the types of queries and the SQL version to be used:

    - For queries that involve fields from only one of the tables in the join, the SQL version of that table's source must be used.

    - If the query involves fields from more than one table in the join, the limited version of SQL developed for file based data sources must be used.
  • Limitations exist in ArcGIS version 8.2 for queries on joined layers and tables that involve date type fields.

    Queries with dates are only successful when using the limited version of SQL developed for file based data sources. If a file based data is not being used, the expression must be forced to use this format. This can be done by making sure that the query expression involves fields from more than one join table.

    For example, if Table1 and Table2 are joined and are both from a personal geodatabase, the following expression will fail:

    Table1.date = date #11/11/2001#

    To make it work properly, it is possible to format the query as follows:

    Table1.date = date '11/11/2001' AND "Table2.FID" is Not Null

    Since the query involves fields from both tables, the limited SQL version must be used. If the "Table2.FID" 'is not NULL expression is True for all rows', only the rows with Table1.date = date '11/11/2001' will be selected.

    There is a second limitation when building a query on a date field for a joined table. When creating a join using Oracle SDE data and the option to 'Keep all Records', the ANSI/ISO date syntax must be used.

    For example, if Table1 and Table2 are joined and are both from an Oracle SDE database, the following expression will fail:

    SDE.Table2.date = TO_DATE('2004-11-13 00:00:00','YYYY-MM-DD HH24:MI:SS')

    To make it work properly, it is possible to format the query as follows:

    SDE.Table2.Date = date '2004-11-13'

    If the previous example was used with a join that used the 'Keep only matching records' option, the query using TO_DATE in the WHERE clause will work.