You need to sign in to do that
Don't have an account?
Arthur Almeida 12
Omission Problem in Query Salesforce
When I execute this query
The first time, happen an error:
But the second time, I executed this query, the result with success
Why does this happen?
List<AggregateResult> query = [ SELECT Max(Id) Id, AccountId, Max(CustomField__c) FROM Asset WHERE Status = 'X' AND (CustomField__c = 'Z' OR CustomField__c = 'Y') GROUP BY AccountId LIMIT 1000 ];
The first time, happen an error:
Internal Salesforce.com Error
But the second time, I executed this query, the result with success
Why does this happen?
1 - Query inside loop: I followed this question: Query Causes Salesforce Internal Error (https://developer.salesforce.com/forums/?id=906F000000091y6IAA) because after my query I had a for loop, so I put my query inside this loop, according to the documentation of the Salesforce Working with Very Large SOQL Queries (https://developer.salesforce.com/docs/atlas.en-us.238.0.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm):
"Your SOQL query sometimes returns so many sObjects that the limit on heap size is exceeded and an error occurs. To resolve, use a SOQL query for loop instead, since it can process multiple batches of records by using internal calls to query and queryMore."
The implementation was like this:
unfortunately only this change did not work and the error persisted, well, the first time I execute my query happens an error, but the second time this query, the result with success, so o implemented a second technique:
2 - Self Test: I writed a test class that only executes this large query, I scheduled the test class to run 5 minutes before my automation run, I did following this documentation: Scheduling Run All Apex Tests and emailing results (https://www.forcetree.com/2019/12/scheduling-run-all-apex-tests-and.html)
The implementation was like this:
unfortunately only this change did not work and the error persisted, so implemented a third technique:
3 - Moved the query to inside of the Batch class: Contextualizing, my query stayed inside of the Job, the job pass the results of the query via constructor to the batch because the Batch classes do not support Aggregate in the function Database.getQueryLocator see this documentation: Batch Apex error 'Aggregate query does not support queryMore' (https://help.salesforce.com/s/articleView?language=en_US&type=1&id=000333422), so I chose to put the query in the job, but I had an insight of putting my query inside of the Batch.
The implementation was like this:
And my problem was solved.
All Answers
I have never encountered such issue.
Is this happening everytime, I mean is it very consistent that first time you always get that error and run success every subsquent time?
I can't think of anything other than this that it would have happened at that point of time for some reason within salesforce but not expecting to be happening all the time as I don't see any issus with the SOQL.
You might be getting the error because you're using the system field 'ID' in the aggregate MAX function. I recommend you remove it and add it to the GROUP BY clause instead.
Check out the docs for more information:
https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm
I do not understand, how would it be my query?
the second time, this was the result:
1 - Query inside loop: I followed this question: Query Causes Salesforce Internal Error (https://developer.salesforce.com/forums/?id=906F000000091y6IAA) because after my query I had a for loop, so I put my query inside this loop, according to the documentation of the Salesforce Working with Very Large SOQL Queries (https://developer.salesforce.com/docs/atlas.en-us.238.0.apexcode.meta/apexcode/langCon_apex_SOQL_VLSQ.htm):
"Your SOQL query sometimes returns so many sObjects that the limit on heap size is exceeded and an error occurs. To resolve, use a SOQL query for loop instead, since it can process multiple batches of records by using internal calls to query and queryMore."
The implementation was like this:
unfortunately only this change did not work and the error persisted, well, the first time I execute my query happens an error, but the second time this query, the result with success, so o implemented a second technique:
2 - Self Test: I writed a test class that only executes this large query, I scheduled the test class to run 5 minutes before my automation run, I did following this documentation: Scheduling Run All Apex Tests and emailing results (https://www.forcetree.com/2019/12/scheduling-run-all-apex-tests-and.html)
The implementation was like this:
unfortunately only this change did not work and the error persisted, so implemented a third technique:
3 - Moved the query to inside of the Batch class: Contextualizing, my query stayed inside of the Job, the job pass the results of the query via constructor to the batch because the Batch classes do not support Aggregate in the function Database.getQueryLocator see this documentation: Batch Apex error 'Aggregate query does not support queryMore' (https://help.salesforce.com/s/articleView?language=en_US&type=1&id=000333422), so I chose to put the query in the job, but I had an insight of putting my query inside of the Batch.
The implementation was like this:
And my problem was solved.