AnsweredAssumed Answered

Odd SelectLayerByAttribute error concerning SQL clause.....

Question asked by peterlen on Sep 29, 2014
Latest reply on Jul 18, 2018 by dougbrowning

Hello - We are using ArcGIS 10.0 and I and using arcpy (Python 2.6) to do some queries. This is what I have:


# Do a location search based on an extent
feature_layer = "in_memory\\featlyr"
arcpy.MakeFeatureLayer_management(fc, feature_layer)
arcpy.SelectLayerByLocation_management(feature_layer, "INTERSECT", AOI, "", "NEW_SELECTION")

# Take the results and add them to a new layer since dealing with the feature layer is time consuming since
#  the total number of features is large
tmpfc = "in_memory\\tmpfc"
arcpy.CopyFeatures_management(feature_layer, tmpfc)
tmplyr = "in_memory\\tmplyr"
arcpy.MakeFeatureLayer_management(tmpfc, tmplyr)

# Go through a loop to do attribute queries based on the initially selected feature.  This will essentially be a
#  loop for sub-queries but do not want to SUBSET_SELECTION with the feature_layer because there will be
#  issues concerning clearing the layer for the next sub-query.
for ......:
   sql = "PRODUCER like 'A%' and (SERIES_ID is  null or LENGTH(TRIM(SERIES_ID)) = 0) and SCALE > 75000"
   arcpy.SelectLayerByAttribute_management(tmplyr, "NEW_SELECTION", sql)  # Error occurs here


Looks like some got cut off due to the code highlighting.  Here is the full SQL:

sql = "PRODUCER like 'A%' and (SERIES_ID is null or LENGTH(TRIM(SERIES_ID)) = 0) and SCALE > 75000"


The error that I encounter is an "Invalid expression" error.  The error is due to the "LENGTH(TRIM(SERIES_ID))" part of the SQL.  Now, if I use my feature_layer as my argument to the arcpy.SelectLayerByAttribute_management call, there is no error and everyting runs fine.  I only get the error when I use the SQL syntax with my tmplyr.


I don't know what is different in the feature_layer that does not cause the error, as compared with the tmplyr.



Any thoughts?


Thanks - Peter