You can translate the question and the replies:

Salesforce GROUP BY

We have a data source connection to Salesforce. The "Delegate GROUP BY" is enabled. After creating a base view to a Salesforce API, we are able to query data from the from API appropriately. When we attempt a simple GROUP BY with a limit of 10 records, we receive the error: "Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch","errorCode":"EXCEEDED_ID_LIMIT". I see that the Salesforce Denodo documentation states: "Take into account that Salesforce limits to 2,000 the number of rows a query with GROUP BY can return." Even though we limited the row count to 5, we are still receiving this error. Is the 2,000 row limit based on the number rows in the table? Or is it the limit?
user
15-02-2024 19:57:43 -0500
code

1 Answer

Hi, It's my understanding that you get a limit related error from a Salesforce data source despite trying to limit the results with the LIMIT clause in your query. In the data source you have activated the option to delegate GROUP BY to Salesforce. It seems that the limit of 2000 rows might not only relate to the aggregated result set, but even to the records used in the aggregation, as hinted in [this thread](https://salesforce.stackexchange.com/questions/212238/aggregated-query-too-many-query-rows). An alternative could be adding a WHERE criteria to the query, to see whether reducing the data set results gives you results without error. This would positively influence the overall reduction of the records managed. Another alternative could be to deactivate GROUP BY delegation option referred in the Virtual DataPort Administration Guide, section [Salesforce Sources](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/salesforce_sources/salesforce_sources#:~:text=of%20the%20API.-,Delegate%20GROUP%20BY.%20Select%20to%20push%20down%20the%20GROUP%20BY%20clause%20to%20Salesforce.%20Take%20into%20account%20that%20Salesforce%20limits%20to%202%2C000%20the%20number%20of%20rows%20a%20query%20with%20GROUP%20BY%20can%20return.,-Include%20deleted%20and), however this impacts on the amount of data sent from Salesforce to Denodo. The aggregation operation will be done by Denodo, which will imply more use of the resources of the Denodo Server. In this case, using the Cache could be something to consider. Hope this helps.
Denodo Team
20-02-2024 11:12:32 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here