function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
bryansosa01bryansosa01 

How to pull out soql queries from loops?

Here is my code:  

 

global class CalculoPanjer implements Database.Batchable < sObject > {


    global final Date fechaal;
    global final string query;




    global Database.QueryLocator start(Database.BatchableContext BC) {


        return Database.getQueryLocator(query);
    }




    global void execute(Database.BatchableContext BC, List < sObject > scope) {
        List < Panjer__c > accns = new List < Panjer__c > ();

        double acom = 0;


        for(sObject s: scope) {

            Panjer__c a = (Panjer__c) s;
            if(a.Fecha_al__c == fechaal && a.n__c > 0) {


                    List < Cartera_por_Banda__c >
                    cpb = [SELECT j__c, vj__c, ej__c, uj__c
                        FROM Cartera_por_Banda__c
                        WHERE Fecha_al__c = : a.Fecha_al__c and j__c <= : a.n__c
                        ORDER BY j__c ];

            

                double acomAn = 0;





                for( Cartera_por_Banda__c c : cpb) {

                  List <Panjer__c>
                        cpb2 = [SELECT An__c
                            FROM Panjer__c
                            WHERE Fecha_al__c = : a.Fecha_al__c and n__c <= : a.n__c - c.vj__c  ];

                    


                    for(  Panjer__c  c2 : cpb2) {

                        acomAn += (c.ej__c / a.n__c) * c2.An__c;
                    }


                }




                a.An__c = acomAn;




                acom += a.An__c;
                a.Acumulado__c = acom;




                accns.add(a);
            }
        }

        update accns;

    }




    global void finish(Database.BatchableContext BC) {

       
    }


}

 

 

I need to make a query per each of the upper level loops. With this code I am passing the governor limits. Maybe with Dynamic queries or Maps, but I have no idea of how to do it.

 

Can anyone help me to improve my code, solving the 200 query limit. I would relly appreciate it.

Sagarika RoutSagarika Rout

I have modified your code, I hope it will work.

 

global class CalculoPanjer implements Database.Batchable < sObject > {

    global final Date fechaal;
    global final string query;

    global Database.QueryLocator start(Database.BatchableContext BC) {


        return Database.getQueryLocator(query);
    }
	
    global void execute(Database.BatchableContext BC, List < sObject > scope) {
        List < Panjer__c > accns = new List < Panjer__c > ();
        double acom = 0;
		Map<ID,Panjer__c> mapofPanjer = new Map<ID,Panjer__c>();
		
        for(sObject s: scope) {
            Panjer__c a = (Panjer__c) s;
            if(a.Fecha_al__c == fechaal && a.n__c > 0) {
			   mapofPanjer.put(a.ID,a);
            }
        }
		List <Panjer__c> cpb2 = new List <Panjer__c>();
		double acomAn = 0;
		for(Panjer__c panjerObj : mapofPanjer.values){
		 Cartera_por_Banda__c CarteraporBanda = [SELECT j__c, vj__c, ej__c, uj__c
														FROM Cartera_por_Banda__c
														WHERE Fecha_al__c = : panjerObj.Fecha_al__c and j__c <= : panjerObj.n__c
														ORDER BY j__c];
		  Panjer__c PanjerObject = [SELECT An__c
								FROM Panjer__c
								WHERE Fecha_al__c = : panjerObj.Fecha_al__c and n__c <= : panjerObj.n__c - CarteraporBanda.vj__c  ];
          acomAn += (CarteraporBanda.ej__c / panjerObj.n__c) * PanjerObject.An__c;							
	    
	     panjerObj.An__c = acomAn;
         acom += panjerObj.An__c;
         panjerObj.Acumulado__c = acom;
         accns.add(panjerObj);
	   }
        update accns;
     }
     global void finish(Database.BatchableContext BC) {
    }
}

 

 

Regards

Sagarika Rout

SFDC Developer
Discuss with meDiscuss with me

global class CalculoPanjer implements Database.Batchable < sObject > {

global final Date fechaal;
global final string query;

global Database.QueryLocator start(Database.BatchableContext BC) {


return Database.getQueryLocator(query);
}

global void execute(Database.BatchableContext BC, List < sObject > scope) {
List < Panjer__c > accns = new List < Panjer__c > ();


double acom = 0;
for(sObject s: scope) {
Panjer__c a = (Panjer__c) s;
if(a.Fecha_al__c == fechaal && a.n__c > 0) {
accns.add(a);
}
}
List < Cartera_por_Banda__c > cpb = [SELECT j__c, vj__c, ej__c, uj__c
FROM Cartera_por_Banda__c];
List < Cartera_por_Banda__c > cpbRefined = new List < Cartera_por_Banda__c >();

For(Panjer__c SinglePan : accns){
For(Cartera_por_Banda__c SingleCpb: cpb){
if(SingleCpb.Fecha_al__c == SinglePan.Fecha_al__c && SingleCpb.j__c <= SinglePan.n__c ){
cpbRefined.add(SingleCpb);
}
}
}
List <Panjer__c > AllPanjer__c = [select SELECT An__c FROM Panjer__c]; // retrive all panjer Record
set <Panjer__c > cpb2Refined = new set<Panjer__c>(); // accept unique record
for(Panjer__c SinglePan2 :AllPanjer__c){
For(Panjer__c SinglePan : accns){
For(Cartera_por_Banda__c SingleCpb: cpbRefined){
if(SinglePan2.Fecha_al__c == SinglePan.Fecha_al__c && SinglePan2.n__c <= : SinglePan.n__c - SingleCpb.vj__c)
cpb2Refined.add(SinglePan2);
}
}
}
double acomAn = 0;
set <Panjer__c > UpdatePanjer = new set<Panjer__c>();
For(Panjer__c SinglePan : accns){
For(Cartera_por_Banda__c SingleCpb: cpbRefined){
for(Panjer__c SinglePan2 :cpb2Refined){
acomAn += (cpbRefined.ej__c / SinglePan.n__c) * cpb2Refined.An__c;
}
}
SinglePan.An__c = acomAn;
acom += SinglePan.An__c;
SinglePan.Acumulado__c = acom;
UpdatePanjer.add(SinglePan);
}

update UpdatePanjer;
}
global void finish(Database.BatchableContext BC) {
}
}

 

 

 
bryansosa01bryansosa01

Thank you for your response, appreciate it.

I think you understood fully what I wanted to do with the code. I only made a few changes in order to function correctly. Here is the final code. But the problem I am having now is the following: Apex CPU time limit exceeded 

 

I would really appreciate if you could do semething to help me.

 

 

global class CalculoPanjer implements Database.Batchable < sObject > {
global final Date fechaal;
global final string query;
global Database.QueryLocator start(Database.BatchableContext BC) {

return Database.getQueryLocator(query);
}

global void execute(Database.BatchableContext BC, List < sObject > scope) {
List < Panjer__c > accns = new List < Panjer__c > ();


double acom = 0;
for(sObject s: scope) {
Panjer__c a = (Panjer__c) s;
if(a.Fecha_al__c == fechaal && a.n__c > 0) {
accns.add(a);
}
}
List < Cartera_por_Banda__c > cpb = [SELECT j__c, vj__c, ej__c, uj__c , Fecha_al__c
FROM Cartera_por_Banda__c];
List < Cartera_por_Banda__c > cpbRefined = new List < Cartera_por_Banda__c >();

For(Panjer__c SinglePan : accns){
For(Cartera_por_Banda__c SingleCpb: cpb){
if(SingleCpb.Fecha_al__c == SinglePan.Fecha_al__c && SingleCpb.j__c <= SinglePan.n__c ){
cpbRefined.add(SingleCpb);
}
}
}
List <Panjer__c > AllPanjer = [ SELECT An__c, Fecha_al__c, n__c FROM Panjer__c]; // retrive all panjer Record
set <Panjer__c > cpb2Refined = new set<Panjer__c>(); // accept unique record
for(Panjer__c SinglePan2 :AllPanjer){
For(Panjer__c SinglePan : accns){
For(Cartera_por_Banda__c SingleCpb: cpbRefined){
if(SinglePan2.Fecha_al__c == SinglePan.Fecha_al__c && SinglePan2.n__c <= SinglePan.n__c - SingleCpb.vj__c)
cpb2Refined.add(SinglePan2);
}
}
}
double acomAn = 0;
set <Panjer__c > UpdatePanjer = new set<Panjer__c>();
For(Panjer__c SinglePan : accns){
For(Cartera_por_Banda__c SingleCpb: cpbRefined){
for(Panjer__c SinglePan2 :cpb2Refined){
    
    acomAn += (SingleCpb.ej__c / SinglePan.n__c) * SinglePan2.An__c ;
    
}
}
SinglePan.An__c = acomAn;
acom += SinglePan.An__c;
SinglePan.Acumulado__c = acom;
UpdatePanjer.add(SinglePan);

   
    
}
List <Panjer__c > l = new List<Panjer__c >();
l.addAll(UpdatePanjer); 
    
update l;
}
global void finish(Database.BatchableContext BC) {
}
}