Apex Code Development You need to sign in to do that
Don't have an account?
test class issue - System.LimitException: Too many SOQL queries: 101
Hi Expert,
Iam getting error "System.LimitException: Too many SOQL queries: 101" when i write test class for "update Estimated Cost", iam not able to run it,
and getting error too many soql queries, from other apex class- "updatePortfolioActualCost", and i have also writen in my test class- seeAllData = false, but it is checking for the other class too. can anybody help me on this.
test class-
@isTest(seeAllData = false)
private class UpdateEstimatedCostTest
{
static testMethod void verifyEstimatedCost()
{
CloudbyzITPM__Project_Portfolio__c portfolio1 = new CloudbyzITPM__Project_Portfolio__c(Name = 'Porfolio 1', CloudbyzITPM__Estimated_Costs__c=13.00);
CloudbyzITPM__Project_Portfolio__c portfolio2 = new CloudbyzITPM__Project_Portfolio__c(Name = 'portfolio 1.1',CloudbyzITPM__Estimated_Costs__c=17.00);
Test.startTest();
insert portfolio1;
insert portfolio2;
CloudbyzITPM__Project_Portfolio__c portfolio1_1 = [Select id,name,CloudbyzITPM__Estimated_Costs__c from CloudbyzITPM__Project_Portfolio__c where id = :portfolio1.id];
CloudbyzITPM__Project_Portfolio__c portfolio2_2 = [Select id,name,CloudbyzITPM__Estimated_Costs__c from CloudbyzITPM__Project_Portfolio__c where id = :portfolio2.id];
CloudbyzITPM__Project__c affectedPro = new CloudbyzITPM__Project__c(Name = 'Project 1');
// creating project without portfoilio and planned estimated cost
insert affectedPro;
CloudbyzITPM__Project__c affectedPro1 = new CloudbyzITPM__Project__c(Name = 'Project 2', CloudbyzITPM__Project_Portfolio__c= portfolio1_1.id );
insert affectedPro1;
CloudbyzITPM__Project__c affectedPro1_1 = [Select id,name,CloudbyzITPM__Planned_Estimated_Costs__c,CloudbyzITPM__Project_Portfolio__c from CloudbyzITPM__Project__c where id = :affectedPro1.id limit 1];
// change the portfolio value of a project
affectedPro1_1.CloudbyzITPM__Project_Portfolio__c = portfolio2_2.id;
update affectedPro1_1;
// remove planned estimated cost of project mapped to a portfoilo
affectedPro1_1.CloudbyzITPM__Planned_Estimated_Costs__c = null;
update affectedPro1_1;
// change the portfolio and update the estimated cost
affectedPro1_1.CloudbyzITPM__Planned_Estimated_Costs__c = 450;
update affectedPro1_1;
//remove portfoilio with actual hours
affectedPro1_1.CloudbyzITPM__Project_Portfolio__c = null;
update affectedPro1_1;
CloudbyzITPM__Project__c affectedPro2 = new CloudbyzITPM__Project__c(Name = 'Project 3', CloudbyzITPM__Planned_Estimated_Costs__c =200.00);
// creating project without portfolio and with estimated cost
insert affectedPro2;
portfolio1_1.CloudbyzITPM__Estimated_Costs__c=affectedPro2.CloudbyzITPM__Planned_Estimated_Costs__c;
update portfolio1;
portfolio2_2.CloudbyzITPM__Estimated_Costs__c=affectedPro2.CloudbyzITPM__Planned_Estimated_Costs__c;
update portfolio2;
portfolio1_1.CloudbyzITPM__Estimated_Costs__c=affectedPro1.CloudbyzITPM__Planned_Estimated_Costs__c;
update portfolio1;
portfolio2_2.CloudbyzITPM__Estimated_Costs__c=affectedPro1.CloudbyzITPM__Planned_Estimated_Costs__c;
update portfolio2_2;
CloudbyzITPM__Project__c affectedPro2_1 = [Select id,name,CloudbyzITPM__Planned_Estimated_Costs__c,CloudbyzITPM__Project_Portfolio__c from CloudbyzITPM__Project__c where id = :affectedPro2.id limit 1];
affectedPro2_1.CloudbyzITPM__Planned_Estimated_Costs__c=300.00;
// change the value of estimated cost of project which is mapped to a portfolio
update affectedPro2_1;
affectedPro2.CloudbyzITPM__Planned_Estimated_Costs__c=100.00;
update affectedPro2;
delete affectedPro2_1;
delete affectedPro;
Test.stopTest();
}
}
Different class where iam getting error- on line no.100
public class updatePortfolioActualCosts
{
public static List<CloudbyzITPM__Project__c> affectedActualCosts = new List<CloudbyzITPM__Project__c>();
public static List<CloudbyzITPM__Project__c> oldaffectedActualCosts = new List<CloudbyzITPM__Project__c>();
public static set<ID> affc = new set<ID>();
public static set<ID> affcold = new set<ID>();
public static void processAfterUpdateActualCosts()
{
// inserting old and new values
affectedActualCosts = (List<CloudbyzITPM__Project__c>)Trigger.New;
oldaffectedActualCosts = (List<CloudbyzITPM__Project__c>)Trigger.Old;
// process after deleting the project
if(Trigger.isDelete)
{
for(CloudbyzITPM__Project__c affc1 : oldaffectedActualCosts)
{
// check whether the portfolio is selescted for the project
if(affc1.CloudbyzITPM__Project_Portfolio__c != null)
{
system.debug('value of affc1'+ affc1);
affc.add(affc1.CloudbyzITPM__Project_Portfolio__c);
}
}
system.debug('this is the value'+ affc);
// process after selecting the portfolio
if(affc.size()>0)
{
CloudbyzITPM__Project_Portfolio__c fn = [select id,name,CloudbyzITPM__Total_Actual_Cost__c from CloudbyzITPM__Project_Portfolio__c where id in: affc limit 1];
list<CloudbyzITPM__Project__c> affectedActualCosts1 = [select id, name,CloudbyzITPM__Total_Actual_Cost__c, CloudbyzITPM__Project_Portfolio__c from CloudByzITPM__Project__c where CloudbyzITPM__Project_Portfolio__c = :fn.id];
Decimal j=0;
System.debug('@@affectedActualCosts1@@'+affectedActualCosts1+'@@affectedActualCosts1.size'+affectedActualCosts1.size());
if(affectedActualCosts1.size()>0)
{
for(CloudbyzITPM__Project__C afc1 : affectedActualCosts1)
{
System.debug('@@afc1.CloudbyzITPM__Total_Actual_Cost__c@@'+afc1.CloudbyzITPM__Total_Actual_Cost__c);
if(afc1.CloudbyzITPM__Total_Actual_Cost__c !=0 && afc1.CloudbyzITPM__Total_Actual_Cost__c!=null)
// add all the actual cost of the associated project
j = j + afc1.CloudbyzITPM__Total_Actual_Cost__c;
}
}
// put all the roll ups of actual cost to portfolio
if(j>0)
{
System.debug('@@pBUFinal@@');
fn.CloudbyzITPM__Total_Actual_Cost__c = j;
}
else
{
fn.CloudbyzITPM__Total_Actual_Cost__c = 0;
}
update fn;
System.debug('@@fn4@@'+fn);
}
}
else
{
if(affectedActualCosts.size()>0)
{
system.debug('@@@value of affc1'+ affectedActualCosts);
for(integer i = 0; i<affectedActualCosts.size(); ++i)
{
if(affectedActualCosts[i]!= null && affectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c != null && oldaffectedActualCosts!= null && oldaffectedActualCosts[i]!= null && oldaffectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c != null && oldaffectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c != affectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c)
{
system.debug('value of affc1'+ affectedActualCosts[i]);
affc.add(affectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c);
affcold.add(oldaffectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c);
}
if(affectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c != null)
{
system.debug('value of affc1'+ affectedActualCosts[i]);
affc.add(affectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c);
}
else if(oldaffectedActualCosts!= null && oldaffectedActualCosts[i]!= null && oldaffectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c != null)
{
affc.add(oldaffectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c);
}
}
if(affcold.size()>0)
{
system.debug('this is the value'+ affc);
CloudbyzITPM__Project_Portfolio__c fn = [select id,name,CloudbyzITPM__Total_Actual_Cost__c from CloudbyzITPM__Project_Portfolio__c where id in: affcold limit 1];
list<CloudbyzITPM__Project__c> affectedActualCosts1 = [select id, name,CloudbyzITPM__Total_Actual_Cost__c, CloudbyzITPM__Project_Portfolio__c from CloudByzITPM__Project__c where CloudbyzITPM__Project_Portfolio__c = :fn.id];
Decimal j=0;
System.debug('@@affectedActualCosts1@@'+affectedActualCosts1+'@@affectedActualCosts1.size'+affectedActualCosts1.size());
if(affectedActualCosts1.size()>0)
{
for(CloudbyzITPM__Project__C afc1 : affectedActualCosts1)
{
System.debug('@@afc1.CloudbyzITPM__Total_Actual_Cost__c@@'+afc1.CloudbyzITPM__Total_Actual_Cost__c);
if(afc1.CloudbyzITPM__Total_Actual_Cost__c !=0 && afc1.CloudbyzITPM__Total_Actual_Cost__c!=null)
j = j + afc1.CloudbyzITPM__Total_Actual_Cost__c;
}
}
if(j>0)
{
System.debug('@@pBUFinal@@');
fn.CloudbyzITPM__Total_Actual_Cost__c = j;
}
else
{
fn.CloudbyzITPM__Total_Actual_Cost__c = 0;
}
update fn;
System.debug('@@fn4@@'+fn);
}
if(affc.size()>0)
{
system.debug('this is the value'+ affc);
CloudbyzITPM__Project_Portfolio__c fn = [select id,name,CloudbyzITPM__Total_Actual_Cost__c from CloudbyzITPM__Project_Portfolio__c where id in: affc limit 1];
list<CloudbyzITPM__Project__c> affectedActualCosts1 = [select id, name,CloudbyzITPM__Total_Actual_Cost__c, CloudbyzITPM__Project_Portfolio__c from CloudByzITPM__Project__c where CloudbyzITPM__Project_Portfolio__c = :fn.id];
Decimal j=0;
System.debug('@@affectedActualCosts1@@'+affectedActualCosts1+'@@affectedActualCosts1.size'+affectedActualCosts1.size());
if(affectedActualCosts1.size()>0)
{
for(CloudbyzITPM__Project__C afc1 : affectedActualCosts1)
{
System.debug('@@afc1.CloudbyzITPM__Total_Actual_Cost__c@@'+afc1.CloudbyzITPM__Total_Actual_Cost__c);
if(afc1.CloudbyzITPM__Total_Actual_Cost__c !=0 && afc1.CloudbyzITPM__Total_Actual_Cost__c!=null)
j = j + afc1.CloudbyzITPM__Total_Actual_Cost__c;
}
}
if(j>0)
{
System.debug('@@pBUFinal@@');
fn.CloudbyzITPM__Total_Actual_Cost__c = j;
}
else
{
fn.CloudbyzITPM__Total_Actual_Cost__c = 0;
}
update fn;
System.debug('@@fn4@@'+fn);
}
}
}
}
}
Regards,
Deepak
Iam getting error "System.LimitException: Too many SOQL queries: 101" when i write test class for "update Estimated Cost", iam not able to run it,
and getting error too many soql queries, from other apex class- "updatePortfolioActualCost", and i have also writen in my test class- seeAllData = false, but it is checking for the other class too. can anybody help me on this.
test class-
@isTest(seeAllData = false)
private class UpdateEstimatedCostTest
{
static testMethod void verifyEstimatedCost()
{
CloudbyzITPM__Project_Portfolio__c portfolio1 = new CloudbyzITPM__Project_Portfolio__c(Name = 'Porfolio 1', CloudbyzITPM__Estimated_Costs__c=13.00);
CloudbyzITPM__Project_Portfolio__c portfolio2 = new CloudbyzITPM__Project_Portfolio__c(Name = 'portfolio 1.1',CloudbyzITPM__Estimated_Costs__c=17.00);
Test.startTest();
insert portfolio1;
insert portfolio2;
CloudbyzITPM__Project_Portfolio__c portfolio1_1 = [Select id,name,CloudbyzITPM__Estimated_Costs__c from CloudbyzITPM__Project_Portfolio__c where id = :portfolio1.id];
CloudbyzITPM__Project_Portfolio__c portfolio2_2 = [Select id,name,CloudbyzITPM__Estimated_Costs__c from CloudbyzITPM__Project_Portfolio__c where id = :portfolio2.id];
CloudbyzITPM__Project__c affectedPro = new CloudbyzITPM__Project__c(Name = 'Project 1');
// creating project without portfoilio and planned estimated cost
insert affectedPro;
CloudbyzITPM__Project__c affectedPro1 = new CloudbyzITPM__Project__c(Name = 'Project 2', CloudbyzITPM__Project_Portfolio__c= portfolio1_1.id );
insert affectedPro1;
CloudbyzITPM__Project__c affectedPro1_1 = [Select id,name,CloudbyzITPM__Planned_Estimated_Costs__c,CloudbyzITPM__Project_Portfolio__c from CloudbyzITPM__Project__c where id = :affectedPro1.id limit 1];
// change the portfolio value of a project
affectedPro1_1.CloudbyzITPM__Project_Portfolio__c = portfolio2_2.id;
update affectedPro1_1;
// remove planned estimated cost of project mapped to a portfoilo
affectedPro1_1.CloudbyzITPM__Planned_Estimated_Costs__c = null;
update affectedPro1_1;
// change the portfolio and update the estimated cost
affectedPro1_1.CloudbyzITPM__Planned_Estimated_Costs__c = 450;
update affectedPro1_1;
//remove portfoilio with actual hours
affectedPro1_1.CloudbyzITPM__Project_Portfolio__c = null;
update affectedPro1_1;
CloudbyzITPM__Project__c affectedPro2 = new CloudbyzITPM__Project__c(Name = 'Project 3', CloudbyzITPM__Planned_Estimated_Costs__c =200.00);
// creating project without portfolio and with estimated cost
insert affectedPro2;
portfolio1_1.CloudbyzITPM__Estimated_Costs__c=affectedPro2.CloudbyzITPM__Planned_Estimated_Costs__c;
update portfolio1;
portfolio2_2.CloudbyzITPM__Estimated_Costs__c=affectedPro2.CloudbyzITPM__Planned_Estimated_Costs__c;
update portfolio2;
portfolio1_1.CloudbyzITPM__Estimated_Costs__c=affectedPro1.CloudbyzITPM__Planned_Estimated_Costs__c;
update portfolio1;
portfolio2_2.CloudbyzITPM__Estimated_Costs__c=affectedPro1.CloudbyzITPM__Planned_Estimated_Costs__c;
update portfolio2_2;
CloudbyzITPM__Project__c affectedPro2_1 = [Select id,name,CloudbyzITPM__Planned_Estimated_Costs__c,CloudbyzITPM__Project_Portfolio__c from CloudbyzITPM__Project__c where id = :affectedPro2.id limit 1];
affectedPro2_1.CloudbyzITPM__Planned_Estimated_Costs__c=300.00;
// change the value of estimated cost of project which is mapped to a portfolio
update affectedPro2_1;
affectedPro2.CloudbyzITPM__Planned_Estimated_Costs__c=100.00;
update affectedPro2;
delete affectedPro2_1;
delete affectedPro;
Test.stopTest();
}
}
Different class where iam getting error- on line no.100
public class updatePortfolioActualCosts
{
public static List<CloudbyzITPM__Project__c> affectedActualCosts = new List<CloudbyzITPM__Project__c>();
public static List<CloudbyzITPM__Project__c> oldaffectedActualCosts = new List<CloudbyzITPM__Project__c>();
public static set<ID> affc = new set<ID>();
public static set<ID> affcold = new set<ID>();
public static void processAfterUpdateActualCosts()
{
// inserting old and new values
affectedActualCosts = (List<CloudbyzITPM__Project__c>)Trigger.New;
oldaffectedActualCosts = (List<CloudbyzITPM__Project__c>)Trigger.Old;
// process after deleting the project
if(Trigger.isDelete)
{
for(CloudbyzITPM__Project__c affc1 : oldaffectedActualCosts)
{
// check whether the portfolio is selescted for the project
if(affc1.CloudbyzITPM__Project_Portfolio__c != null)
{
system.debug('value of affc1'+ affc1);
affc.add(affc1.CloudbyzITPM__Project_Portfolio__c);
}
}
system.debug('this is the value'+ affc);
// process after selecting the portfolio
if(affc.size()>0)
{
CloudbyzITPM__Project_Portfolio__c fn = [select id,name,CloudbyzITPM__Total_Actual_Cost__c from CloudbyzITPM__Project_Portfolio__c where id in: affc limit 1];
list<CloudbyzITPM__Project__c> affectedActualCosts1 = [select id, name,CloudbyzITPM__Total_Actual_Cost__c, CloudbyzITPM__Project_Portfolio__c from CloudByzITPM__Project__c where CloudbyzITPM__Project_Portfolio__c = :fn.id];
Decimal j=0;
System.debug('@@affectedActualCosts1@@'+affectedActualCosts1+'@@affectedActualCosts1.size'+affectedActualCosts1.size());
if(affectedActualCosts1.size()>0)
{
for(CloudbyzITPM__Project__C afc1 : affectedActualCosts1)
{
System.debug('@@afc1.CloudbyzITPM__Total_Actual_Cost__c@@'+afc1.CloudbyzITPM__Total_Actual_Cost__c);
if(afc1.CloudbyzITPM__Total_Actual_Cost__c !=0 && afc1.CloudbyzITPM__Total_Actual_Cost__c!=null)
// add all the actual cost of the associated project
j = j + afc1.CloudbyzITPM__Total_Actual_Cost__c;
}
}
// put all the roll ups of actual cost to portfolio
if(j>0)
{
System.debug('@@pBUFinal@@');
fn.CloudbyzITPM__Total_Actual_Cost__c = j;
}
else
{
fn.CloudbyzITPM__Total_Actual_Cost__c = 0;
}
update fn;
System.debug('@@fn4@@'+fn);
}
}
else
{
if(affectedActualCosts.size()>0)
{
system.debug('@@@value of affc1'+ affectedActualCosts);
for(integer i = 0; i<affectedActualCosts.size(); ++i)
{
if(affectedActualCosts[i]!= null && affectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c != null && oldaffectedActualCosts!= null && oldaffectedActualCosts[i]!= null && oldaffectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c != null && oldaffectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c != affectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c)
{
system.debug('value of affc1'+ affectedActualCosts[i]);
affc.add(affectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c);
affcold.add(oldaffectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c);
}
if(affectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c != null)
{
system.debug('value of affc1'+ affectedActualCosts[i]);
affc.add(affectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c);
}
else if(oldaffectedActualCosts!= null && oldaffectedActualCosts[i]!= null && oldaffectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c != null)
{
affc.add(oldaffectedActualCosts[i].CloudbyzITPM__Project_Portfolio__c);
}
}
if(affcold.size()>0)
{
system.debug('this is the value'+ affc);
CloudbyzITPM__Project_Portfolio__c fn = [select id,name,CloudbyzITPM__Total_Actual_Cost__c from CloudbyzITPM__Project_Portfolio__c where id in: affcold limit 1];
list<CloudbyzITPM__Project__c> affectedActualCosts1 = [select id, name,CloudbyzITPM__Total_Actual_Cost__c, CloudbyzITPM__Project_Portfolio__c from CloudByzITPM__Project__c where CloudbyzITPM__Project_Portfolio__c = :fn.id];
Decimal j=0;
System.debug('@@affectedActualCosts1@@'+affectedActualCosts1+'@@affectedActualCosts1.size'+affectedActualCosts1.size());
if(affectedActualCosts1.size()>0)
{
for(CloudbyzITPM__Project__C afc1 : affectedActualCosts1)
{
System.debug('@@afc1.CloudbyzITPM__Total_Actual_Cost__c@@'+afc1.CloudbyzITPM__Total_Actual_Cost__c);
if(afc1.CloudbyzITPM__Total_Actual_Cost__c !=0 && afc1.CloudbyzITPM__Total_Actual_Cost__c!=null)
j = j + afc1.CloudbyzITPM__Total_Actual_Cost__c;
}
}
if(j>0)
{
System.debug('@@pBUFinal@@');
fn.CloudbyzITPM__Total_Actual_Cost__c = j;
}
else
{
fn.CloudbyzITPM__Total_Actual_Cost__c = 0;
}
update fn;
System.debug('@@fn4@@'+fn);
}
if(affc.size()>0)
{
system.debug('this is the value'+ affc);
CloudbyzITPM__Project_Portfolio__c fn = [select id,name,CloudbyzITPM__Total_Actual_Cost__c from CloudbyzITPM__Project_Portfolio__c where id in: affc limit 1];
list<CloudbyzITPM__Project__c> affectedActualCosts1 = [select id, name,CloudbyzITPM__Total_Actual_Cost__c, CloudbyzITPM__Project_Portfolio__c from CloudByzITPM__Project__c where CloudbyzITPM__Project_Portfolio__c = :fn.id];
Decimal j=0;
System.debug('@@affectedActualCosts1@@'+affectedActualCosts1+'@@affectedActualCosts1.size'+affectedActualCosts1.size());
if(affectedActualCosts1.size()>0)
{
for(CloudbyzITPM__Project__C afc1 : affectedActualCosts1)
{
System.debug('@@afc1.CloudbyzITPM__Total_Actual_Cost__c@@'+afc1.CloudbyzITPM__Total_Actual_Cost__c);
if(afc1.CloudbyzITPM__Total_Actual_Cost__c !=0 && afc1.CloudbyzITPM__Total_Actual_Cost__c!=null)
j = j + afc1.CloudbyzITPM__Total_Actual_Cost__c;
}
}
if(j>0)
{
System.debug('@@pBUFinal@@');
fn.CloudbyzITPM__Total_Actual_Cost__c = j;
}
else
{
fn.CloudbyzITPM__Total_Actual_Cost__c = 0;
}
update fn;
System.debug('@@fn4@@'+fn);
}
}
}
}
}
Regards,
Deepak
In your first class, I see that you are creating two 'CloudbyzITPM__Project_Portfolio__c' records and inserting them indivually. One better way to do it is creating a list of 'CloudbyzITPM__Project_Portfolio__c' and inserting the list once. Also you are updaing various values of 'affectedPro1_1' instance of 'CloudbyzITPM__Project__c' object and performing update for each and every time. You can perform all the changes and update the record once. One thing I would do in my test classes is, adding the 'Test.startTest()' after I finish performing few DMLs. i.e. in the middle of the method, instead of adding it in the top of the method.
Please try these and let me know if it solves the issue.
Regards,
Sankaranarayanan V