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

[DuncanHornby]

Can you edit your question and use the advance editor option (top right) then >> button to set syntax highlighting, it makes code a lot easier to read...

[PeterLen]

Duncan - Thanks for the heads up about the syntax highlighting.  It cut a little of it off but it is easier to read.  I added the full SQL syntax to help.

[JamesCrandall]

"LENGTH(TRIM(SERIES_ID))" just doesn't make sense.  Can you express the full query you wish to apply?

I can see two potential issues:

1.  You are not evaluating any specific field.

SELECT * FROM <layer_name> WHERE <field name> = 0

2. TRIM(SERIES_ID) I suspect that SERIES_ID is an integer value being represented as text (hence the need to issue TRIM).  If so, then I'd recommend you actually fix the datasource --- if it's actually integers you need to store, then set the field to that type.  Then there is no gymnastics required to TRIM strings or worry about other such issues because the data is correct.

[PeterLen]

James - Thanks for the reply.  The SERIES ID is a string field even though most values are straight numbers.  Some do have alpha characters so the column had to be string-based.  I added an example of the full SQL to give you a better idea.  Again, the SQL does not produce an error when using the feature_layer but it does when using the tmplyr which I traced to the use of the functions LENGTH and TRIM.  Not sure what the difference is except feature_layer contains all 500K + features, including the selected ones from the location search.  The tmplyr just has the selected features.  If I simply state SERIES_ID = '', then there is no error, but I can't use that since some values do have spaces for values (it is not my data to clean up).

Thank s- Peter

[curtvprice]

In many SQL versions the syntax should be this (the "IS" is required with the keyword NULL):

SERIES_ID IS NOT NULL

or

NOT (SERIES_ID IS NULL)

Just an aside, in Python you can enter long strings using parentheses like this:

sql = ("PRODUCER LIKE 'A%' AND "
    "(SERIES_ID IS NOT NULL OR LENGTH(TRIM(SERIES_ID)) = 0) AND "
    "SCALE > 75000") 

[PeterLen]

Update....... it appears the problem is related to copying the selected features to an in-memory feature class and then making a lyer off of that class:

• tmpfc = "in_memory\\tmpfc" 
• arcpy.CopyFeatures_management(feature_layer, tmpfc) 
• tmplyr = "in_memory\\tmplyr" 
• arcpy.MakeFeatureLayer_management(tmpfc, tmplyr) 

The tmplyr object, albeit a feature layer like the original feature_layer object is, is really not the same type of layer.  I essentially did this process via ArcMap and the toolbox.  When I look at the original feature_layer attribute table, the SERIES_ID column has an asterisk next to it and the column names in the "Select by Attributes" dialog appear without quotes, like OBJECTID, ITEM_ID, SERIES_ID.  When I look at the tmplyr attribute table, the SERIES_ID column does not have an asterisk and the column names in the "Select by Attributes" dialog contains quotes like, "OBJECTID", "ITEM_ID", "SERIES_ID".  something is obviously different.  When i try to run my SQL in the "Select by Attributes" dialog I get the error when using the tmplyr but not the orignal feature_layer, just as my Python results had.

[PeterLen]

Update 2.... it looks like the difference is the original feature_layer object has an SDE/Oracle source, where the LENGTH and TRIM functions are valid within SQL statements. The tmplyr which originally was populated via a CopyFeatures action was stored in a file geodatabase source, which does not accept LENGHT and TRIM within the SQL.

[Zeke]

Not sure about TRIM, but you might try LEN as an alternative for LENGTH.

edit: for TRIM, maybe this page would help.

[JamesCrandall]

LTRIM() or RTRIM() would be what to use.

Try:

sql = "PRODUCER like 'A%' and (SERIES_ID is  null or LEN(LTRIM(RTRIM(SERIES_ID))) = 0)