topic Re: Odd SelectLayerByAttribute error concerning SQL clause..... in Python Questions

Odd SelectLayerByAttribute error concerning SQL clause.....

PeterLen — Mon, 29 Sep 2014 18:27:55 GMT

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

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

DuncanHornby — Mon, 29 Sep 2014 18:43:56 GMT

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...

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

JamesCrandall — Mon, 29 Sep 2014 19:48:12 GMT

"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.

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

PeterLen — Mon, 29 Sep 2014 23:37:07 GMT

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.

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

PeterLen — Mon, 29 Sep 2014 23:42:19 GMT

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

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

curtvprice — Tue, 30 Sep 2014 03:38:52 GMT

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

SERIES_ID IS NOT NULL

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")

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

PeterLen — Tue, 30 Sep 2014 15:16:05 GMT

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.

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

PeterLen — Tue, 30 Sep 2014 15:24:52 GMT

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.

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

Zeke — Tue, 30 Sep 2014 17:14:46 GMT

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

edit: for TRIM, maybe this page would help.

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

JamesCrandall — Tue, 30 Sep 2014 19:02:25 GMT

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)

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

PeterLen — Tue, 30 Sep 2014 19:36:30 GMT

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.

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

curtvprice — Wed, 01 Oct 2014 00:45:58 GMT

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.

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

JamesCrandall — Wed, 01 Oct 2014 12:08:20 GMT

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?

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

PeterLen — Wed, 01 Oct 2014 12:32:39 GMT

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

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

JamesCrandall — Wed, 01 Oct 2014 13:03:44 GMT

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.

Re: Odd SelectLayerByAttribute error concerning SQL clause.....

DougBrowning — Wed, 18 Jul 2018 17:16:46 GMT

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