4

We have a problem when selecting Features from a Feature Class in ArcMAP 10.2 using the Iterate Feature Selection tool in ModelBuilder.

Our dataset has a Attribute Field with two character codes like 'AO', 'IN','BY','TR' and 'IS'. However the tool crashes because the SQL Statment cannot be evaluated. IN, IS and BY are reserved Words in SQL. How can i escape this behaviour?


This is the Error message in the log:

 ERROR 999999: Error executing function.
 An invalid SQL statement was used.
 An invalid SQL statement was used. [BY]
 An invalid SQL statement was used. [SELECT * FROM BY]
 Failed to execute (Feature Class to Feature Class).

And the model is this:

https://www.dropbox.com/s/msgo810uz28tprg/Modelbuilder.jpg

  • Where do you store your data and would you please post the SQL expression you use in your model? – Alex Tereshenkov Dec 20 '13 at 8:49
  • The data is stored in a FileGeoDatabase and the 'wrong' SQL statement only appears in the log. The selection is based on the Iterate Feature Selection tool in Model builder where you can specify the field that should be selected. – Michael Dec 20 '13 at 9:00
  • Would you mind posting a model or a part of it where the SQL statement parameter used? See, the thing is that you should not have any problem doing selection of features with the SQL expression like "FieldName in ( 'BY', 'IN', 'IS')" - the values are just the strings. Please post the exact error statement you get in the log, too. – Alex Tereshenkov Dec 20 '13 at 9:20
  • @AlexTereshenkov, yes you are absolutely right. I have updated the question. As You can see this tool preforms the statement SELECT * FROM BY, which crashes. If the Attribte field is 'AO' or something that is not a reserved SQL name, then it works. – Michael Dec 20 '13 at 9:55
2

You use the wrong syntax for the selection. When you want to select all features that have the value "BY" in a certain field, you are supposed to use this syntax:

FieldName = 'BY'

or if you want to select multiple features that might have several possible values:

FieldName in ('BY', 'IN', 'IS')"

Please refer to the help section Building query expression to learn more about the syntax which might be different depending on the data source.

When using the Iterate Feature Selection iterator in ModelBuilder, the Make Feature Layer tool is run behind the scenes each time the software will iterate through the feature to create a layer. And in this case, if you have chosen to group by a field "Type", you can see the following in the progress dialog:

Executing (Make Feature Layer): MakeFeatureLayer C:\ArcGIS\Default.gdb\layer feature_class ""Type" = 'BY'" # #

I have calculated a text field Type with three values: 'BY', 'IN', and 'IS'. I get the feature classes created properly and there are no SQL syntax errors. As you, I am on 10.2. enter image description here To learn more about iterators in ModelBuilder, consider going through a nice tutorial Esri has uploaded on ArcGIS.com: Tutorials: ModelBuilder. There is one specifically for Iterate Feature Selection where a similar workflow is discussed and explained.

  • When you double click on the tool Iterate Feature Selection then you have the option to group by field, which is then doing all the selection and expressions (Selecting all features with 'BY'). There is no way to change the SQL statement. – Michael Dec 20 '13 at 10:19

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.