4

I need to get all features from feature class where their field value matches to input array. To achive this one may set IQueryFilter.WhereClause property similar to 'FIELD_NAME IN (array[0], array[1], array[2],...., array[n-1])'. But the size of array might be quite big.

The questions are how long IN statement can be? And can I define it at runtime?

Thanks in advance!

  • Are you doing this via code, or in the User interface? How is the array currently stored? – Devdatta Tengshe Nov 13 '13 at 7:26
  • 3
    This depends on the underlying database. Note that besides the total query length, some databases also impose restrictions on the count of elements in a single IN clause. – Petr Krebs Nov 13 '13 at 8:51
  • I'm doing it through the C# code. Array is rarefied. >"some databases also impose restrictions on the count of elements in a single IN clause." - I expect this. That's why I'm asking can I define this at runtime. – Eduard Lepner Nov 13 '13 at 11:17
  • 1
    You'd need to specify which RDBMS you're using in order to get an answer – Vince Nov 13 '13 at 13:29
  • Now we are targeting on Oracle. But I'd like to support SQL and personal gdb also. – Eduard Lepner Nov 13 '13 at 17:40
7

The exact number of explicit terms in an IN clause varies by RDBMS. Oracle limits you to 1000 explicit terms, while the SQL-Server doc only mentions "several thousands".

The real issue is that an IN clause is not very efficient. If you have more than a few hundred terms, you'd likely get a faster query by loading a temporary table with the values, then doing a join (or an IN to a subselect, which is implemented as a join). In ArcGIS, selection sets can be used for this purpose (vice explicitly creating a temporary table, but the implementation is the same).

2

I didn't find a way how to find out exact size of IN statement and then safaly build it but I have found a workaround. I have reduced described problem to search the rows satisfying the following where clause: (OBJECTID IN (123, 456,789)) AND FIELD1 = x AND FIELD2 = y AND .... I.e. the statement has IN statement with very big list of numbers and arbitrary AND statements.

Here is the workaround:

  1. I used ITable.Select to create empty selection set.
  2. Then populate this set with ISelectionSet.AddArray with necessary OIDs. It allows to avoid from using IN statement like in WHERE clause OBJECTID IN (123, 456, 789)
  3. Apply additional additional where caluses to selection set ISelectionSet.SearchSearch(IQueryFilter, bool, out ICursor).
0

How can the below be replaced to use a variable in place of hard code values filter.WhereClause = featureClass.OIDFieldName + " IN (1,2,3,4,5,6)";

string strFIDS="1,2,3,4,5,6";

Does the below work ? filter.WhereClause = featureClass.OIDFieldName + " IN (strFIDS)";

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.