I am getting the "Too Many SOQL queries" error on the code below when executing as anonymous APEX in my sandbox. But by my reading of "best practices" I thought I'm doing things right - I was careful not to do DML inside any loop. The list I'm trying to update is about 4000 records.
What am I doing wrong? How can I fix it?
List<Case> caseList = [
SELECT ID
,CASENUMBER
,ISCLOSED
,CreatedDate
,CLOSEDDATE
,status
,Request_Received_Date_Time__c
,Case_Resolution_Date_Time__c
,Request_To_Resolution_Business_Hours__c
FROM CASE
WHERE ISCLOSED = true
and calendar_year(createddate) = 2018
AND Request_To_Resolution_Business_Hours__c = NULL
];
System.debug('caseList Size: ' + caseList.size()); // that list is going to be about 4000 records for 2018
BusinessHours bh = [
SELECT FridayEndTime
,FridayStartTime
,Id
,IsActive
,IsDefault
,LastModifiedById
,LastModifiedDate
,MondayEndTime
,MondayStartTime
,Name
,SaturdayEndTime
,SaturdayStartTime
,SundayEndTime
,SundayStartTime
,SystemModstamp
,ThursdayEndTime
,ThursdayStartTime
,TimeZoneSidKey
,TuesdayEndTime
,TuesdayStartTime
,WednesdayEndTime
,WednesdayStartTime FROM BusinessHours where Name='xxxxx'
LIMIT 1
];
For(Case case_record : caseList){
try{
DateTime startDate, endDate;
if(case_record.Request_Received_Date_Time__c!=null) { startDate = case_record.Request_Received_Date_Time__c; }
else{ startDate = case_record.CreatedDate; }
if(case_record.Case_Resolution_Date_Time__c!=null) { endDate = case_record.Case_Resolution_Date_Time__c; }
else{ endDate = case_record.ClosedDate; }
Double requestToResolution = BusinessHours.diff(bh.Id, startDate, endDate)/3600000.0;
case_record.Request_To_Resolution_Business_Hours__c = requestToResolution;
System.debug('Case ' + case_record.CASENUMBER + ' Request_To_Resolution_Business_Hours__c updated to: ' + requestToResolution);
} catch(Exception e) {
System.debug('General Unhandled Exception on case ' + case_record.CASENUMBER + ' ' + e.getMessage());
}
}
Update caseList;