Odd SelectLayerByAttribute error concerning SQL clause.....

[PeterLen]

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

[PeterLen]

Hmmmm.... I thought that might be it, but when I tried those, I still get the error.  I am totally outside of my application now and working with the layers in an ArcMap example.  For the file GDB layer, I took everything out of the SQl except for things like LEN(SERIES_ID) = 0 as well as LTRIM(SERIES_ID) = '', but both produce the error.  I'll keep looking in that vein though.

[curtvprice · Accepted solution]

The help page with the details as to what the file gdb supports is here:

ArcGIS Help (10.2, 10.2.1, and 10.2.2): SQL Expressions for query expressions used in ArcGIS.

TRIM (with options for left, right, and both trims)  and CHAR_LENGTH are both supported.

Also -- string comparison results can vary between data sources as much as the SQL syntax does - so I strongly suggest doing a unique tabulation of your values and testing them interactively in table view in ArcMap.

[DougBrowning]

I just discovered that some SQL statements that work just fine on a GDB do not work on in_memory datasets.

My issue was CAST.  It works just fine in ArcMap and when I hit a GDB directly.  But with a in_memory it says invalid SQL statement.

I found this post where Esri says they are different but do not give any doc or hints as to what is and what is not supported.  https://community.esri.com/thread/165846  Please Esri document this stuff!  It costs lots of hours.

Thanks

[JamesCrandall]

I guess I don't understand why you do not actually fix the underlying problem.  You have identified a need to issue TRIM(), so why don't you fix the datasource now and eliminate the need to perform this in your SQL?

Also, print out the field names for the in_memory fc's your are generating.  Perhaps the query fails because you are not stating the correct fields?

[PeterLen]

Thanks for all the replies guys.  I am sure it is just a matter of syntax for the file GDB type.  As for the fixing the data rather than mucking around with this syntax issue, we don't own the data or even the SQL syntax.  The various SQL items (which I loop though) are used to perform sub-queries on the original location query.   Someone else owns the data we are querying as well as the SQL statements that we use so I have no flexibility in fixing any data issues.  If I can find the right syntax I will still have to do some Python string replacement.  It is on the backburner for now, but I will get back to it.  Thanks - Peter

[JamesCrandall]

Then I would seriously question the validity of the data you are being provided.  If the provider cannot or will not perform a simple process (like removing leading/trailing spaces in a string field), then I would have some pause as to the correctness of the data in its entirety.  Anytime a developer has to perform silly gymnastics is only compounding the potential problems and potential incorrect, inconsistent and overall poor output/results.

With that said, I would suggest to go back to your OP and simplify the query to bare-bones minimum.  For example I'd take this:

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)

and try this:

sql = "PRODUCER like 'A%'"    
arcpy.SelectLayerByAttribute_management(tmplyr, "NEW_SELECTION", sql) 

Does it work?

Yes?  Then expand it a bit:

sql = "PRODUCER like 'A%' AND SCALE > 75000"    
arcpy.SelectLayerByAttribute_management(tmplyr, "NEW_SELECTION", sql) 

No?  Then something basic like "PRODUCER" is not actually a field in your in_memory fc.