Unable to make complex queries with .NET SDK

[AntoineKlein]

Hello everyone !

I'm working on an ArcGIS Pro add-in and I need to make SQL queries using keywords "SELECT COUNT(field)", "GROUP BY" and "HAVING".
At the moment, I use 2 methods to execute queries :
- Geodatabase.Evaluate(QueryDef)
- FeatureLayer.Search(QueryFilter)
For QueryDef and QueryFilter we have to set a Subfields property to get the columns, but we can't use the COUNT keyword.
I also tried to add a "GROUP BY" in the PostfixClause property but it doesn't work either...

So I would like to know if it is possible to do that, and if yes, how, or if there is a workaround to do that

Thank you for your advices.

[GKmieliauskas · Accepted solution]

Hi,

For count you can use method GetCount(QueryFilter) Method—ArcGIS Pro from table or featureclass.

For records grouping I would suggest you look at the GroupBy Property (TableStatisticsDescription)—ArcGIS Pro.

[AntoineKlein]

Thanks for your answer, can I use these methods together ?

I would like to execute a query like this :

 SELECT count(node.idnode) AS nbr_elements, node.idnode
   FROM node
  GROUP BY node.idnode
 HAVING count(node.idnode) > 1;

 I'll try it.

I had another idea to this, but I'm not sure either, I thought to use a database view instead of, but I don't know if it is possible to query a view from ArcGIS PRO, is it ?

[RichRuh]

Antoine,

I think you can do everything but the HAVING clause using the table statistics methods described here.

--Rich

[AntoineKlein]

Hi,

Thank you for your help, finally I used database view to do this, it will be easier in our project context, but I'll probably need this later.

Have a nice day !