0

I have an aggreateresult[] soql as follows:

AggregateResult[] groupedResults = [SELECT id, Account__c, Account__r.Account_ID__c, MAX(Account_c__C) 
                                             FROM Custom_Month__c WHERE ID IN:MonthlyList 
                                               group by id, Account__c, Date__c, Account__r.Account_ID__c];

When I tried to refer above fetched ids in the below list,

accList2 = [SELECT Account__c, Account__r.Account_ID__c  FROM MonthlyList WHERE ID IN: groupedResults[0]]

I am getting the error as

IN operator must be used with an iterable expression

I need to fetch the id from the aggregateresult query. Can anyone help me with that? Please suggest

  • What is the use of grouping your query when you are grouping by Id, Id will always be unique and hence it will never be grouped. – Vijay Ganji Oct 14 at 12:14
1

You need to get the value from the aggregate result - you can only use a (list of) IDs/strings or SObjects when specifying a binding for the IN SOQL keyword.

Since you haven't named (using aliases) the fields you return in the aggregate result you will need to do something like:

Id theId = (Id) groupedResults[0].get('expr0');

to get the first field (the Id).

Once you have that you can use it in your SOQL like:

accList2 = [SELECT ... FROM MonthlyList WHERE Id = :theId];

See the documentation for further information.

Note that this is only using the first group's Id. If you want to use all groups' Ids then you need to loop through the groupedResults and add the Ids to a list of IDs. You can then use that list of IDs as a binding in the SELECT with an IN keyword. This would be like:

List<Id> theIds = new List<Id>();

for (AggregateResult result : groupedResults) {
    theIds.add((Id) result.get('expr0'));
}

and a query like:

accList2 = [SELECT ... FROM MonthlyList WHERE Id IN :theIds];
  • when I updated my query with your suggested one Id theID = groupedResults[0].get('expr0'); I got the error as "Illegal assignment from Object to Id" Please suggest – Skumar Oct 14 at 10:13
  • 1
    I simply forgot to add the cast. Code snippet updated. – Phil W Oct 14 at 10:47
  • Thanks @PhilW, When I ran the page, I got an error. In the debug log, it was shown as "Invalid id: 8.202". When I ran the code in anonymous window, I got null pointer exception. It seems like, instead of id, I am getting Account_c__C field in the place of id. 8.202 is the Account_c__C field value. Please suggest – Skumar Oct 14 at 11:52
  • 1
    The expr0 name should get the first unaliased field in the aggregate result. If you want to be sure what you are getting, update the Aggregate Query to alias the various fields. E.g. "[SELECT Id id, Account__c account, Account__r.Account_ID__c accountId, MAX(Account_c__C) maxAccountC FROM Custom_Month__c WHERE ..." then use get('id') to get the ID field from an aggregate result. – Phil W Oct 14 at 11:57
  • here in my theID, I am storing multiple IDs. When I refer to the string as =:theID in accList2, I am getting error in my vf page and my debug log is showing empty for acclist2. – Skumar Oct 15 at 9:56

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.