[GIS] SQL-query in arcpy – SELECT WHERE feature between two values

arcpypythonquerysql

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?

Best Answer

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

Related Question