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
    where do you store the gdb? – Alex Tereshenkov Oct 28 '14 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 '15 at 6:06
  • perhaps failing because the numbers are being compared as strings instead when BETWEEN used, while < forces numeric(?) – matt wilkie Mar 9 '15 at 21:38
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, privacy policy and cookie policy

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