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?

3
  • 3
    where do you store the gdb? Commented 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 Commented Jan 13, 2015 at 6:06
  • perhaps failing because the numbers are being compared as strings instead when BETWEEN used, while < forces numeric(?) Commented Mar 9, 2015 at 21:38

1 Answer 1

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

Your Answer

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

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