‘Invalid SQL’ error when using the BETWEEN operator in Arcgis

24 Aug

Ok, so I was basically trying to use a simple SQL operator to select in a  shapefile  polygons that had and “ID” number between 1-1500. It was  impossible for Arcgis 9.3 to understand the common syntax of :

SELECT *FROM soils
WHERE “ID” between 1 AND 1500;

It always returned an error saying that an invalid SQL statement  was used.

After a while thinking about it I discovered that this other form actually works!

SELECT *FROM soils WHERE “ID” >=1 AND “ID” <=1500

I really don’t understand why it doesn’t accept the first form as I think is correct, but anyway, if somebody have the same problem this is a work around… 🙂

Advertisements

4 Responses to “‘Invalid SQL’ error when using the BETWEEN operator in Arcgis”

  1. Dave Potts August 24, 2010 at 5:52 pm #

    The syntax is correct, which database where you using? and what ddl for the table?

    • patriciamurrieta August 25, 2010 at 9:44 am #

      Hi Dave! Well, basically is just a simple selection query on an Arcgis shapefile. I dont really know what ddl arcgis uses but if you could explain me that it will be of great use to me. It seems that I’m not the only person with this problem, I have found other people that have the same issue with SQL statements in arcgis.
      Thank you for the post, you are the first one to read my blog 😀

  2. Pedro Santos December 28, 2010 at 9:42 am #

    Hi,

    Adding to Dave’s comment, DDL stands for Data Definition Language which in database terms includes all SQL statements used to physically manipulate database objects (create or remove tables, add new columns to existing tables, etc).

    I believe the issue here (may be just a bug in ArcGIS…) is related to the fact that ESRI is trying to translate the standards of SQL to query a non-relational database environment (the shapefile’s properties). As such, and given the problem you report, I would guess that not all standard SQL has been implemented by ESRI into ArcGIS.

    Although just based on assumptions (haven’t tested it nor validated it elsewhere), I believe that the BETWEEN operator is one of those exceptions – although quite useful in SQL it seems that it has not been (successfully) implemented by ESRI into ArcGIS. Given that as you mention in your original post there is a simple workaround, I doubt they even bothered 😉

    Finally, keep on with the blog – I’ve found a couple of useful tips in here 🙂

  3. Eric March 2, 2012 at 9:59 pm #

    Sorry that this may be two years too late, but I think it may have to do with the limitation of the .dbf file format being db4. I just ran across this issue recently from a student.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: