DISTINCT, ORDER BY, COUNT use in 10.1...

Question

Hi All,

Architecture is :

10.1 SP1 java
10.1 ArcMap
10.1 FGDB

Implementing SOE in java.  Is it not possible to this with a 10.1 FGDB?//define the filter.
filter = new QueryFilter();
filter.setSubFields("DISTINCT " + CADASTRE_FIELD_NAME);
filter.setWhereClause(PROV_FIELD_NAME + " = '" + province + "'");

What I am trying to do is run a particular query off a map service.  I could do this with using SDE, but seems it's crapping out when I swapped it out for FGDB.

How do you accomplish this task?

BillZborowski · Answer

Thanks for the response.  Using your suggestion I modified the code to read:
//define the filter.
filter = new QueryFilter();
filter.setSubFields(CADASTRE_FIELD_NAME);
filter.setWhereClause(PROV_FIELD_NAME + " = '" + province + "'");
   
IQueryFilterDefinition2 filterDefinition = (IQueryFilterDefinition2)filter;
filterDefinition.setPrefixClause("DISTINCT " + CADASTRE_FIELD_NAME);

I want to do it this way to avoid bringing another technology stack (Oracle/JNDI/etc) into the overall architecture if at all possible.

NeilClemmons_GISinc · Answer

What the query filter object does is build a SQL query using the property values you give it. It looks something like this: SELECT FROM WHERE It doesn't parse the property values you give it - it simply puts them into the query. Different data sources implement different flavors of SQL. What works in one may or may not work in another. The Subfields property is intended to be a simple list of field names. The fact that you can also add keywords such DISTINCT is simply a byproduct of how the object creates the SQL queries. Because it is not an intended use of the property, you should not include anything but field names in the list unless you can guarantee that your query will always execute against a data source that supports the keywords you are inserting. If you need to perform queries that do more than what the query filter object supports then you shouldn't use it. Instead, you can connect to the data source directly and execute the required queries (this is a non-ArcObjects approach). You can also use ArcObjects classes, such as IDataStatistics and IQueryFilterDefinition, that are specifically written for additional query support. There is also the IWorkspace.ExecuteSQL method which may be of some use in certain cases. ISqlSyntax may also be used to build queries for specific data sources. Hope this helps.