AnsweredAssumed Answered

Why is querying against a date on left part of a join such an issue?

Question asked by billfox on Dec 27, 2017
Latest reply on Dec 29, 2017 by billfox

Example:

  • feature class in one enterprise geodatabase
  • table to join to the feature class from a second enterprise geodatabase

 

Verify Expression Error: The expression was verified successfully, but no records were returned.

After adding the bit about objectid from the joined table > 0

The expression was successfully verified

 

Reference:

SQL reference for query expressions used in ArcGIS—Help | ArcGIS Desktop 

"Known limitations

Querying against a date on the left part (first table) of a join only works with file-based data sources, such as file geodatabases, shapefiles, and DBF tables. However, there is a possible workaround for working with non-file-based data, like personal geodatabase data and ArcSDE data as described below.

Querying against a date on the left part of a join will be successful when using the limited version of SQL developed for file-based data sources. If you are not using such a data source, you can force the expression 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 a feature class and a table (FC1 and Table1) are joined and are both from a personal geodatabase, the following expressions will fail or return no data:

FC1.date = date #01/12/2001# FC1.date = date '01/12/2001'

To query successfully, you can create a query as follows:

FC1.date = date '01/12/2001' and Table1.OBJECTID > 0

Since the query involves fields from both tables, the limited SQL version will be used. In this expression, Table1.OBJECTID is always > 0 for records that matched during join creation, so this expression is true for all rows that contain join matches.

To ensure that every record with FC1.date = date '01/12/2001' is selected, use the following query:

FC1.date = date '01/12/2001' and (Table1.OBJECTID IS NOT NULL OR Table1.OBJECTID IS NULL)

This query will select all records with FC1.date = date '01/12/2001', whether or not there was a join match for each particular record."

Outcomes