+ Start a Discussion
swapna9swapna9 

Too many SOQL queries: 101

Hi All,

 

how to keep query out side of the loop .

 

my code is as below..

 

 public List<Double> getAmount()
    {   
    List<Training_Schedule__c> Training= new List<Training_Schedule__c>();
    List<Revenue_Schedule__c> revenue=new List<Revenue_Schedule__c>();
    revenue=[select id,Date__c from Revenue_Schedule__c where Price_Book_Name__c=:price[0].Id];     
    for(Integer k=0;k<revenue.size();k++)    
    {       
        Training=[select id,Amount__c,Revenue_Date__c from Training_Schedule__c where  Revenue_Schedule__c=:revenue[k].Id ];
        amt=0;    
        for(Integer j=0;j<Training.size();j++)
        {         
         if(Training[j].Amount__c!=null)
          {      
          amt=amt+(Training[j].Amount__c);                                 
          }                      
        }       
       Amount.add(amt);           
    }
    return Amount;            
   }

 

Thanks...

AmulAmul

Use collection like Map.

 

And Use Aggregate Query.

 

What is your exace Requirement. Please explore more.

kiranmutturukiranmutturu

set<Revenue_Schedule__c>  revenueIds = new set<Revenue_Schedule__c>();

 

Training=[select sum(Amount__c) alias from Training_Schedule__c where  Revenue_Schedule__c in :revenueIds group by fieldname ];

 

then u will get the amount

Navatar_DbSupNavatar_DbSup

Hi,

 

Try below code:


 public List<Double> getAmount()
{
List<Training_Schedule__c> Training= new List<Training_Schedule__c>();
List<Revenue_Schedule__c> revenue=new List<Revenue_Schedule__c>();
list<id> ids=new list<id>();
revenue=[select id,Date__c from Revenue_Schedule__c where Price_Book_Name__c=:price[0].Id];
for(revenue r:[select id,Date__c from Revenue_Schedule__c where Price_Book_Name__c=:price[0].Id])
{
ids.add(r.id);
}
//Training=[select id,Amount__c,Revenue_Date__c from Training_Schedule__c where Revenue_Schedule__c in ids];
amt=0;
for(Training t:[select id,Amount__c,Revenue_Date__c from Training_Schedule__c where Revenue_Schedule__c in: ids])//0;j<Training.size();j++)
{
if(t.Amount__c!=null)
{
amt=amt+(t.Amount__c);
}
}
Amount.add(amt);
return Amount;
}

 

Did this answer your question? If not, let me know what didn't work, or if so, please mark it solved. 

swapna9swapna9

Thanks for ur reply...

 

I tried to use set and map.but i was unable to fix it.

 

My requirement is Under Revenu_Schedule__c related list is Training_Schedule__c . in visual force page i am displaying all revenue shedule records.in each  record i am trying to display amount  which is roll up of training schedule amount(taring_Schedule__c.Amount__C).For example under R-001 revenue schedule record, 3 training schedule records are there.each traing schedule have amount 1000.i want to display 3,000 in R-001 record..

 

if revenue schedule records is more than 50 then it is displaying too many soql queries:101.

i kept query out side of the loop.then it is not rolling up properly..again it is giving too many apex script statemnts:200001

 

Please tel me how to put map in this method...

 

 

juppyjuppy

Hi,

you need to prepare appropriate collections before accumulating the Amounts - just as you would for a bulk trigger.

Something like the following should do the trick.

 

Note my comment about what is being returned; you may find that returning a list of amounts indexed by Revenue is more useful to you.

 

Also, should you need to, you can use the same technique to return amounts by revenue by pricebook - instead of using the first entry in your pricebook list to retrieve Revenue records, use a collection.

You would then return a map of <id, map<id, list<double>>>

Let me know if you need any further assistance!

 

    public list<Double> getAmount()
    {
        set<id>                    set_RevenueIds                        = new set<id>();
        map<id, list<double>>    map_RevenueIdtoTrainingAmounts        = new map<id, list<double>>();
    
        for (Revenue_Schedule__c [] arrRevenue : [    select    id,
                                                            Date__c
                                                    from    Revenue_Schedule__c
                                                    where    Price_Book_Name__c=:price[0].Id
                                                ])
        {
            for (Revenue_Schedule__c sRevenue : arrRevenue)
            {
                set_RevenueIds.add(sRevenue.Id);
            }
        }     
    
        for (Training_Schedule__c [] arrTraining :    [    select    id,
                                                                Amount__c,
                                                                Revenue_Date__c,
                                                                Revenue_Schedule__c
                                                        from    Training_Schedule__c
                                                        where     Revenue_Schedule__c in :set_RevenueIds
                                                    ])
        {
            for (Training_Schedule__c sTraining : arrTraining)
            {
                if (sTraining.Amount__c != null)
                {
                    list<double> li_Amounts;
    
                    if (!map_RevenueIdtoTrainingAmounts.containsKey(sTraining.Revenue_Schedule__c))
                    {
                        li_Amounts = new list<double>();
                    }
                    else
                    {
                        li_Amounts = map_RevenueIdtoTrainingAmounts.get(sTraining.Revenue_Schedule__c);
                    }

                    li_Amounts.add(sTraining.Amount__c);                                

                    map_RevenueIdtoTrainingAmounts.put(sTraining.Revenue_Schedule__c, li_Amount);
                }                      
            }
        }
    
        // The collection map_RevenueIdtoTrainingAmounts has a list of Amount indexed by Revenue_Schedule id
        // This might be more useful to you than a list of all Amounts
        
        // If not, you can convert this to a plain list as follows
        
        list<double> Amount = new list<double>();
        
        for (list<double> li_Amounts : map_RevenueIdtoTrainingAmounts.values())
        {
            Amount.addAll(li_Amounts);
        }
        
        return Amount;

    }

 

 

swapna9swapna9

Thanks for ur reply,

 

But it is displaying grand total of training schedule amount related to all revenue schedule records in first record of revenue schedule.

It is not rolling up training schedule amount  related to each record of revenue schedule.

 

can u please tel me where i need to do change in the code...

 

swapna9swapna9

Thanks for ur reply,

 

I used ur code.. but it is not rolling  training schedule amount.Suppose revenue_schedule_record (R-001) have 3 training schedule records which is having amount 1000,1500,2000.so it sholud rollup all amount 4,500.but it is dispalying 3 times to each revenue schedule record(R-001) as 1000,1500,2000.Again it is displaying error as Collection size exceeds. 1382:1000

 

so can u please tel me where i can do modify....

 

Thanks in advance...

juppyjuppy

No worries - I only wrote the code to return a list because that's what your original code snippet returned.

Here you are - this returns a map of Revenue id to a rolled-up amount:

 

    public map<id, double> getAmount()
    {
        set<id>                    set_RevenueIds                        = new set<id>();
        map<id, double>            map_RevenueIdtoTrainingAmounts        = new map<id, double>();
    
        for (Revenue_Schedule__c [] arrRevenue : [    select    id,
                                                            Date__c
                                                    from    Revenue_Schedule__c
                                                    where    Price_Book_Name__c=:price[0].Id
                                                ])
        {
            for (Revenue_Schedule__c sRevenue : arrRevenue)
            {
                set_RevenueIds.add(sRevenue.Id);
            }
        }     
    
        for (Training_Schedule__c [] arrTraining :    [    select    id,
                                                                Amount__c,
                                                                Revenue_Date__c,
                                                                Revenue_Schedule__c
                                                        from    Training_Schedule__c
                                                        where     Revenue_Schedule__c in :set_RevenueIds
                                                    ])
        {
            for (Training_Schedule__c sTraining : arrTraining)
            {
                if (sTraining.Amount__c != null)
                {
                    double Amount;
    
                    if (!map_RevenueIdtoTrainingAmounts.containsKey(sTraining.Revenue_Schedule__c))
                    {
                        Amount = 0;
                    }
                    else
                    {
                        Amount = map_RevenueIdtoTrainingAmounts.get(sTraining.Revenue_Schedule__c);
                    }

                    Amount += sTraining.Amount__c;                                

                    map_RevenueIdtoTrainingAmounts.put(sTraining.Revenue_Schedule__c, Amount);
                }                      
            }
        }
        
        return map_RevenueIdtoTrainingAmounts;
    }

swapna9swapna9

Thanks for ur support,

I am trying your new code...

but in the below code i am getting error...

else
       {
             Amount= map_RevenueIdtoTrainingAmounts.get(sTraining.Revenue_Schedule__c);

            //In the above line i am getting error as  Illegal assignment from LIST<Double> to Double
        }

 

can u please suggest me where i can do modification....

 

Thanks in advance....

juppyjuppy

Look at the revised code - map_RevenueIdtoTrainingAmounts has changed to a map of id and a double :smileywink:

swapna9swapna9

Really Thank you so..... much to ur support......

 

but now in visual force page is displaying ids instead of values...

 

So i changed the code as

 

public List<Double> getAmount()
    {
        set<id> set_RevenueIds = new set<id>();
        map<id, double> TrainingAmounts = new map<id, double>();
      
    
        for (Revenue_Schedule__c [] arrRevenue : [select  id,Date__c from Revenue_Schedule__c  where Price_Book_Name__c=:price[0].Id order by date__c])
        {
            for (Revenue_Schedule__c sRevenue : arrRevenue)
            {
                set_RevenueIds.add(sRevenue.Id);
            }
        }     
    
        for (Training_Schedule__c [] arrTraining : [select id, Amount__c, Revenue_Date__c, Revenue_Schedule__r.Id
                                                     from Training_Schedule__c  where Revenue_Schedule__r.Id in :set_RevenueIds order by Revenue_Date__c])
        {

        for (Training_Schedule__c sTraining : arrTraining)
            {
                if (sTraining.Amount__c != null)
                {
                    Double Amount;
                
                if (!TrainingAmounts.containsKey(sTraining.Revenue_Schedule__r.Id))
                    {
                        Amount = 0;
                    }
                    else
                    {
                        Amount= TrainingAmounts .get(sTraining.Revenue_Schedule__r.Id);
                    }
                    Amount +=sTraining.Amount__c;                    
                    TrainingAmounts.put(sTraining.Revenue_Schedule__c,Amount );                                                       
                 }
            }
           
        }        
         List<double> col = new List<double>();
         col = TrainingAmounts.values() ;
         return col;
    }


Its rolled up properly...but it is not displaying values date wise.i used order by also in query...

 

my visual force page code is below.

 

<table valign="top" height="10px">
      
        <tr><td><b>Sales Forecast</b></td></tr>  
    <apex:repeat value="{!Amount}" var="A">   
        <tr><td height="10px" valign="top" align="center">
        <apex:outputLabel value="{!A}">
        </apex:outputLabel></td></tr> 
        
    </apex:repeat>
    </table>

 

please guide me.....