Can't join/export feature layer with definition expression set on related table

MKF62 — Sat, 11 Dec 2021 21:39:51 GMT

I have a polygon feature layer with a related table that holds each polygon's associated records. The two tables are related based on the MgmtTractID field. In my script, I want to be able to pull just records and their associated polygons for a certain year. So, if the year was 2010, I would set the definition expression on the table to be "FocalRefID = '{...}' and YearTreated = 2010 or FocalRefID = '{...}' and YearTreated = 2012" and then after that has been applied, I would join the result to my polygon layer to limit the polygons shown to only those that were active in 2010. When I do this manually in Desktop, it works just how I would expect. When I try to do this in python, the join results in zero polygon features and I get a DBMS error that says my feature layer doesn't exist which is perplexing...

Code that doesn't work:

#Create feature layers/get table
tractFC = hbMgmt_db_con + r'\HabitatManagement.DBO.MgmtTracts'
tractLyr = arcpy.MakeFeatureLayer_management(tractFC, "MgmtTracts")
tractAttrbTbl = hbMgmt_db_con + r'\HabitatManagement.DBO.MgmtAttrb'

#Do a bunch of stuff

if "MgmtTracts" in layers:
    #Generate the query expression
    #...
    #...
    #Get the appropriate records in the related table and export them to the geodatabase
    tractAttrbTV = arcpy.MakeTableView_management(tractAttrbTbl, 'tractAttrbTV', expression)
    arcpy.TableToTable_conversion(tractAttrbTV, exportPath, "MgmtTractAttrb")
    #Use the new table view to join to the tract layer and select the polygon tracts to export
    arcpy.AddJoin_management(tractLyr, "MgmtTractID", tractAttrbTV, "MgmtTractID")
    arcpy.FeatureClassToFeatureClass_conversion(tractLyr, exportPath, "MgmtTracts")‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

If I comment out the "AddJoin" line, the script runs normally and the resulting feature layer that gets exported contains all the features with no limitations put on it. If I leave the AddJoin line as is and comment out the FeatureClassToFeatureClass line, the join carries out normally, but the result is empty. If I leave both lines uncommented I get this error. Given what I just explained, I'm 99% sure this error is bogus and it does not have anything to do with my DBMS since it obviously can find the tractLyr just fine otherwise the FC2FC line would fail, even when the AddJoin line was commented out. I do not know where the 'oHabitatManagement' is coming from though.

File "D:\NET_Projects\HabitatMapGPServices\ShipHabitatData\ShipHabitatData.py", line 175, in <module>
arcpy.FeatureClassToFeatureClass_conversion(tractLyr, exportPath, "MgmtTracts")
File "C:\Program Files (x86)\ArcGIS\Desktop10.6\ArcPy\arcpy\conversion.py", line 1910, in FeatureClassToFeatureClass
raise e
arcgisscripting.ExecuteError: ERROR 999999: Error executing function.
Underlying DBMS error [[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'oHabitatManagement'.] [DBJoin1]
Underlying DBMS error [[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'oHabitatManagement'.] [DBJoin1]
Underlying DBMS error [[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'oHabitatManagement'.] [DBJoin1]
Underlying DBMS error [[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'oHabitatManagement'.] [DBJoin1]
The table was not found. [MgmtTracts]
Failed to execute (FeatureClassToFeatureClass).

UPDATE:

It apparently has something to do with the query expression I create. When I changed the expression to be something much simpler (e.g. YearTreated = 2010), the code ran without issue. This is what I do to create the definition query to be applied to the table:

if "MgmtTracts" in layers:
#Get FocalReferenceIDs of interest and popuplate dictionary
frIDs = {}
states = []
with arcpy.da.SearchCursor(allCIPs, ['FocalRefID', 'StateID']) as sCursor:
    for row in sCursor:
        frIDs[row[0]] = years
        states.append(row[1])
#Initiate new list to hold expressions for final query
expSegements = []
#Create query expression with FocalReferenceIDs and years of interest
for k,v in frIDs.items():
    for value in v:
        expression = "FocalRefID = '" + k + "' and YearTreated = {0}".format(value)
        expSegements.append(expression)
#Convert expression segments to one long string
expression = " or ".join(expSegements)‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The above results in a definition query being generated that looks like this:

FocalRefID = '{5B61BFBB-A99A-4EB5-ABAC-89A0D143DBDD}' and YearTreated = 2009 or
 FocalRefID = '{5B61BFBB-A99A-4EB5-ABAC-89A0D143DBDD}' and YearTreated = 2015 or
 FocalRefID = '{A31CF0B8-68A1-4927-9A19-6232626588DD}' and YearTreated = 2009 or
 FocalRefID = '{A31CF0B8-68A1-4927-9A19-6232626588DD}' and YearTreated = 2015‍‍‍‍‍

I'm not seeing anything wrong with that. It applied to the table just fine, it only becomes a problem when trying to join the table result to the feature layer. From what I can deduce, the problem comes at the "or" part of the statement. If I use an expression like this:

FocalRefID = '{...}' and YearTreated = 2009

It'll do the job. If I extend it with an 'or' statement like this:

FocalRefID = '{...}' and YearTreated = 2009 or FocalRefID = '{...}' and YearTreated = 2015

It'll fail. Is there some other way I should be building the SQL expression?

topic Can't join/export feature layer with definition expression set on related table in Python Questions

Can't join/export feature layer with definition expression set on related table