AnsweredAssumed Answered

ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

Question asked by matt_stutts on Dec 4, 2015
Latest reply on Dec 7, 2015 by matt_stutts

My python script in ArcMap has an initial dialog box with two inputs: REG_CODE (values are in a list and include 'AKR', 'IMR', 'NER', etc) and Selection_type which offers 'NEW_SELECTION', 'REMOVE_FROM_SELECTION', etc.

My code runs just fine unless I pick the value 'NER'. Then I get this error:

Traceback (most recent call last): File "C:\Temp\SelectRegionalCREnterpriseData.py", line 99, in selectRelatedRecords(originTable, destinationTable, primaryKeyField, foreignKeyField) File "C:\Temp\SelectRegionalCREnterpriseData.py", line 96, in selectRelatedRecords arcpy.SelectLayerByAttribute_management(destinationTable, selectType, whereClause) File "c:\program files (x86)\arcgis\desktop10.2\arcpy\arcpy\management.py", line 6688, in SelectLayerByAttribute raise e ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).

I can verify that my layers and tables have a valid field called REG_CODE, that 'NER' does exist as an attribute value, and that there are no associated NULL values in the CR_ID field.

The script does two things... 1) goes through each layer, and selects features where REG_CODE = (say, NER) 2) Finds all records in CR_Catalog table where REG_CODE = and then selects the related records in the CR_Link table.

 

 

 

import arcpy, os, string
import arcpy.mapping as MAP




#Needed CR layers as well as Catalog and Link tables should be loaded into current mxd
mxd = MAP.MapDocument("CURRENT")
df = MAP.ListDataFrames(mxd)[0]



#set Reg_Code value for regional data into a sql expression
fld = arcpy.AddFieldDelimiters(arcpy.env.workspace, "REG_CODE")
sqlExp = fld + " = " + " '{0}' ".format(arcpy.GetParameterAsText(0))
arcpy.AddMessage(sqlExp)
#set selection type
selectType = arcpy.GetParameterAsText(1)





#Make lists of all feature layers and another list of all tables in dataframe
layers = MAP.ListLayers(mxd, "", df)
tables = MAP.ListTableViews(mxd, "", df)



#Set destinationTable (CR_Link) to empty string. This gets populated only if Link table is loaded into mxd.
# else, the select layer by attribute tool doesn't run
destinationTable = ""
originTable = ""



#used in generating list of related CR_IDs for match Catalog to Link table. Related by CR_ID field.
primaryKeyField = "CR_ID"
foreignKeyField = "CR_ID"



#These will be used to get records in Link that are related to Catalog where #Reg_Code = <input>

for table in tables:
  descTable = arcpy.Describe(table)
  tableNameList = (descTable.name).split(".")
  tableName = tableNameList[-1]
   if tableName == "CR_Catalog":
  originTable = table
   elif tableName == "CR_Link":
  destinationTable = table
   else:
   pass





#Main code to select all records in Catalog with matching Reg_code and then getting related records in Link table

if not str(destinationTable) == "" and not str(originTable) == "":
   def buildWhereClauseFromList(originTable, primaryKeyField, valueList):
   # Add DBMS-specific field delimiters
  fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(originTable).path, primaryKeyField)

   # Determine field type
  fieldType = arcpy.ListFields(originTable, primaryKeyField)[0].type

   # Add single-quotes for string field values
   if str(fieldType) == 'String':
  valueList = ["'%s'" % value for value in valueList]

   # Format WHERE clause in the form of an IN statement
  whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(map(str, valueList)))
   return whereClause



 

 def selectRelatedRecords(originTable, destinationTable, primaryKeyField, foreignKeyField):
   ## Defines the record selection from the record selection of the OriginTable
   ## and applys it to the DestinationTable using a SQL WHERE clause built
   ## in the previous defintion"""

   # Set the SearchCursor to look through the selection of the OriginTable
  sourceIDs = set([row[0] for row in arcpy.da.SearchCursor(originTable, primaryKeyField, sqlExp)])



  

# Establishes the where clause used to select records from DestinationTable
  whereClause = buildWhereClauseFromList(destinationTable, foreignKeyField, sourceIDs)

   # Process: Select Layer By Attribute
  arcpy.SelectLayerByAttribute_management(destinationTable, selectType, whereClause)

   # Process: Select related records between OriginTable and DestinationTable
  selectRelatedRecords(originTable, destinationTable, primaryKeyField, foreignKeyField)

   del tables, tableName, tableNameList, primaryKeyField, foreignKeyField
   del originTable, destinationTable



#If Link isn't in mxd, send a message
else:
  arcpy.AddMessage("CR_Link and CR_Catalog tables must be added to dataframe in order to create selection in CR_Link")


for lyr in layers:
  fieldList = arcpy.ListFields(lyr.dataSource, "REG_CODE")
   if len(fieldList) == 1:
  arcpy.SelectLayerByAttribute_management(lyr, selectType, sqlExp)
   else:
   pass


del lyr, layers, 
del sqlExp, fld, mxd, df, fieldList 

Outcomes