ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

Question

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.

I'm losing it. Is NER a reserved word of some sort? Makes no sense...

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 

Answer 1

Without a more concise code snippet to try and debug I think any answer to your question, as it stands, may be need to be part guesswork.

However, I had the same error message today from the same tool and like you I was also using the buildWhereClauseFromList and selectRelatedRecords functions that I found at Efficiently selecting related records using ArcPy?

I tracked it down to field values that included names like O'BRIEN and O'TOOLE. This was expanding to a where clause like:

"FieldName IN ('O'BRIEN','O'TOOLE')"

when SelectLayerByAttribute requires:

"FieldName IN ('O''BRIEN','O''TOOLE')"

I was able to get past it by changing:

valueList = ["'%s'" % value for value in valueList]

to

valueList = ["'%s'" % value.replace("'","''") for value in valueList]

in the buildWhereClauseFromList function.