Forum Discussion
Group By for Salesforce Query
Follows.
The SQL looks similar to yours
DataMashup.Trace Error: 24579 : {"Start":"2015-08-14T17:16:15.4316580Z","Action":"Engine/IO/Web/Request/GetResponse","RequestMethod":"GET","RequestUri":"https://emea.salesforce.com/services/data/v29.0/query?q=SELECT Commitment__c,COUNT(Id) FROM Investment__c WHERE (Status__c<>'Cancelled') GROUP BY Commitment__c","RequestHasContent":"False","RequestHasHeaders":"False","RequestHasTimeout":"True","UseCache":"True","Exception":"Exception:\r\nExceptionType: System.Net.WebException, System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b12a3c456789e000\r\nMessage: The remote server returned an error: (400) Bad Request.\r\nStackTrace:\n at System.Net.HttpWebRequest.GetResponse()\r\n at Microsoft.Mashup.Engine1.Library.Web.Request.CreateResponse(ResourceCredentialCollection credentials)\r\n at Microsoft.Mashup.Engine1.Library.Web.Request.GetResponseCore(ResourceCredentialCollection credentials)\r\n at Microsoft.Mashup.Engine1.Library.Common.RetryPolicy.Execute[TResult](IEngineHost host, Func`1 func)\r\n at Microsoft.Mashup.Engine1.Library.Web.Request.GetResponse(ResourceCredentialCollection credentials, RetryPolicy retryPolicy, AuthorizationThrowFunction authorizationFunction)\r\n\r\n\r\n","ProductVersion":"2.25.4095.554 (Release-V2-Private)","ActivityId":"53705023-fdbb-4f70-87cd-10edaf0433a1","Process":"Microsoft.Mashup.Container.NetFX40","Pid":8608,"Tid":5,"Duration":"00:00:00.9311327"}
Are you familiar with Fiddler or other network tools? I'd like to see the error message being returned by Salesforce in this case, but there's no way to do it without looking at your network traffic.
Yes, this is from Fiddler (deleted the auth headers):
GET https://emea.salesforce.com/services/data/v29.0/query?q=SELECT%20Commitment__c%2CCOUNT(Id)%20FROM%20Investment__c%20WHERE%20(Status__c%3C%3E'Cancelled')%20GROUP%20BY%20Commitment__c HTTP/1.1
Accept: */*
User-Agent: Microsoft.Data.Mashup (https://go.microsoft.com/fwlink/?LinkID=304225)
Accept-Encoding: gzip, deflate
Host: emea.salesforce.comHTTP/1.1 400 Bad Request
Date: Wed, 19 Aug 2015 07:03:53 GMT
Expires: Thu, 01 Jan 1970 00:00:00 GMT
Sforce-Limit-Info: api-usage=3983/23600
Content-Type: application/json;charset=UTF-8
Content-Length: 145[{"message":"Aggregate query does not support queryMore(), use LIMIT to restrict the results to a single batch","errorCode":"EXCEEDED_ID_LIMIT"}]
Wow, thanks for doing this. It looks like there's a basic limitation in SOQL which disallows aggregate queries that return more than 2000 rows. I've filed a bug to try to handle this better on our end.