topic Re: Join a Table to a Feature Layer with a Where Clause in Python Questions

Join a Table to a Feature Layer with a Where Clause

DNVT — Thu, 25 Jan 2018 15:56:14 GMT

I am trying to calculate a field in the feature class from a table. The table and feature class have a common field for a join. I want to filter the feature class to only edit rows that have been edited in the past 7 days. I have created a feature layer with a Where Clause filtering all rows that have been edited in the past 7 days. I am unable to join the table to the feature layer. When I make the feature layer without the where clause the join works and I am able to calculate the field. With the Where Clause in Python I receive an "An invalid SQL statement was used" error. I have also tried each scenario in ArcMap. Without the where clause works but when joining with a feature layer with a Where Clause I receive an error in the join validation:

"The number of matching records for the join:
- Unexpected error encountered.
- No matches found by joining"

Has anyone else ran into this problem?

Python code with Where Clause

# Get start and end times for report
start_time= datetime.timedelta(days = 7)
end_time = datetime.datetime.now()
report_time = end_time-start_time #this is the time that gets used to filter records

# Find all records that are later than or = to report_time
arcpy.MakeFeatureLayer_management(lyr, FeatureLyr, dateSearch)

# Join the table to the feature layer and calculate field
arcpy.AddJoin_management (FeatureLyr, JoinField, JoinTable, JoinField)

arcpy.CalculateField_management(FeatureLyr, "TransferField", "!JoinTable.TransferField!", "PYTHON_9.3")

Python code without Where Clause

# Find all records
arcpy.MakeFeatureLayer_management(lyr, FeatureLyr)

# Join the table to the feature layer and calculate field
arcpy.AddJoin_management (FeatureLyr, JoinField, JoinTable, JoinField)

arcpy.CalculateField_management(FeatureLyr, "TransferField", "!JoinTable.TransferField!", "PYTHON_9.3")

Re: Join a Table to a Feature Layer with a Where Clause

DNVT — Thu, 25 Jan 2018 20:39:06 GMT

I am working in an ArcSDE environment. I have tried other Where Clauses other than a date and it works. When I use a date as my Where Clause I keep receiving the error:

ERROR 999999: Error executing function.

An invalid SQL statement was used.

Failed to execute (CalculateField).

Re: Join a Table to a Feature Layer with a Where Clause

RandyBurton — Sun, 12 Dec 2021 04:04:05 GMT

In this line of code

arcpy.MakeFeatureLayer_management(lyr, FeatureLyr, dateSearch)‍‍‍‍

dateSearch looks to be a where clause, but I don't see where/how it is getting initialized. Are you using something like:

# for file geodatabase
dateSearch = "dateField > date '{}'".format(datetime.datetime.strftime(report_time,"%Y-%m-%d %H:%M:%S"))

# result: "dateField = date '2018-01-21 19:01:29'"‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The format of the where clause will vary depending on the database you are using. See SQL reference for query expressions used in ArcGIS.