4

I need to select features between two values in python with arcpy. However, whenever I try to run the code below, it returns with error message "An invalid SQL statement was used".

whereclause = """"X" BETWEEN {0} AND {1}""".format(
                                                  min(X)[0]-1000,
                                                  max(X)[0]+1000)
ap.FeatureClassToFeatureClass_conversion(SamledeMaalinger,ap.env.workspace,"SamledeMaalingerSort", whereclause)

The whereclause returns "X" BETWEEN 555157.0723 AND 557157.0723 so it's not the string that's the issue.

The SQL-query works if I use < and > instead, but the BETWEEN for some reason doesn't.

So what's the issue? Do the SQL-queries in arcpy have limitations?

Improve this question
3
  • 3
    where do you store the gdb?
    – Alex Tereshenkov
    Oct 28, 2014 at 10:35
  • According to About building an SQL expression you should be able to use BETWEEN: You can use greater than (>), less than (<), greater than or equal (>=), less than or equal (<=), and BETWEEN operators
    – Stephen Lead
    Jan 13, 2015 at 6:06
  • perhaps failing because the numbers are being compared as strings instead when BETWEEN used, while < forces numeric(?)
    – matt wilkie
    Mar 9, 2015 at 21:38

1 Answer 1

Reset to default
1

As @AlexTereshnekov commented, the problem may be caused by your spatial data storage choice.

I just used the expression below successfully in a file geodatabase feature class:

OID BETWEEN 1 AND 100

but in a shapefile this expression reported that it was invalid SQL:

"FID" BETWEEN 1 AND 100

My testing used ArcGIS 10.2.2 for Desktop

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.