-4

I have requirement like to display related records related to leads with sic code starts with.So i filtered with using Like operator but i am getting exception 101 exception.But i am not able to bulkify below code.

how to handle in this case.

    public class LeadController 
    {
        public String CurrentId;
        public List<Opportunity> newopplist{get;set;}
        public List<Opportunity> listopp{get;set;}

        public LeadController(ApexPages.StandardController controller) {
         CurrentId=ApexPages.currentPage().getParameters().get('id');
        }

        public void search()
        {
            set<String> cityset=new set<String>();
            Map<String,String> citymap=new Map<String,String>();
            Map<String,String> statemap=new Map<String,String>();
            Map<String,String> Siccodes=new Map<String,String>();
            List<Opportunity> newopplist=new List<Opportunity>();
            for(Lead ld:[Select Id,City,State,SIC_Code_1__c from Lead where Id=:CurrentId ])
            {
                // cityset.add(ld.City);
                if(ld.City <> null)
                {
                    citymap.put(ld.City,ld.City);
                }
                if(ld.state <> null)
                {
                    statemap.put(ld.State,ld.State);
                }
                if(ld.SIC_Code_1__c <> null)
                {
                Siccodes.put(ld.SIC_Code_1__c, ld.SIC_Code_1__c);
                }
            }
            newopplist=[Select Id,Name,Account.name,Number_of_Units__c,StageName,account.SIC_Code__c,Type,
                        Billing_City__c,Billing_State_Province__c,account.SIC_Industry_Name__c,
                        account.No_of_active_units__c from Opportunity where StageName='Closed Won' ];
            listopp=new List<Opportunity>();
            for(Opportunity opp: newopplist)
            {          
                if(!Siccodes.isEmpty())
                {
                    Set<String> codes = new Set<String>();
                    for(String code:Siccodes.keySet())
                    {
                        codes.add(code + '%');
                    }
                    listopp=[Select Id,Name,StageName,Account.name,Number_of_Units__c,account.SIC_Code__c,Type,
                             Billing_City__c,Billing_State_Province__c,account.SIC_Industry_Name__c,
                             account.No_of_active_units__c from Opportunity where account.SIC_Code__c 
                             LIKE :codes and StageName='Closed Won' ORDER BY account.No_of_active_units__c Desc Limit 10];                    

                }
       }
}

I am hitting limits because of this query as per debug logs.

Select Id,Name,StageName,Account.name,Number_of_Units__c,account.SIC_Code__c,Type, Billing_City__c,Billing_State_Province__c,account.SIC_Industry_Name__c, account.No_of_active_units__c from Opportunity where account.SIC_Code__c LIKE :codes and StageName='Closed Won' ORDER BY account.No_of_active_units__c Desc Limit 10

  • When you know the Id why for loop SOQL for Lead. you are querying Opportunity twice and one of them in loop and selecting all closed won you will hit 50000 limit error in production for that. Not sure what is your requirement. – Avijit Chakraborty Jun 20 '17 at 13:26
  • Even i bulkified above query i dont have any other criteria to filter @battery.cord – Pavan tej Jun 20 '17 at 13:28
  • Your code is not bulkified. It's clear you did not read the linked duplicate. I suggest taking a look at the later sections of the answer, which cover removing queries from loops, the primary cause of your error. – battery.cord Jun 20 '17 at 13:31
  • 1
    I would argue this is NOT a duplicate because the linked question does not deal with the question of how to bulkify a "LIKE" query. Which, granted, is not an easy proposition. – Charles T Jun 20 '17 at 17:19
  • @CharlesT agree with you because i am able to resolve above one with check with map like contains sic.But i am losing my expected results. – Pavan tej Jun 21 '17 at 9:45
1

One of the most important best practices in Apex programing, and in most languages, is to never include DB acceses within loops. Specifically in Salesforce, there are Governor Limits that reign the platform. The reason for this is so an specific org doesn't consume too many resources, since the servers are shared. See this post for more information the Multi Tenant Architecture they use.

Now, coming back to your issue, you are breaking with this practice exactly here:

        for(Opportunity opp: newopplist)
        {          
            if(!Siccodes.isEmpty())
            {
                Set<String> codes = new Set<String>();
                for(String code:Siccodes.keySet())
                {
                    codes.add(code + '%');
                }
                listopp=[Select Id,Name,StageName,Account.name,Number_of_Units__c,account.SIC_Code__c,Type,
                         Billing_City__c,Billing_State_Province__c,account.SIC_Industry_Name__c,
                         account.No_of_active_units__c from Opportunity where account.SIC_Code__c 
                         LIKE :codes and StageName='Closed Won' ORDER BY account.No_of_active_units__c Desc Limit 10];                    

            }
   }

The best way to handle this proble is to isolate DB acceses. I would recomend you to bring all the records you need in a single query and then distribute them in different structures like Sets or Maps, so you can later easily access them. For example, in an scenario where you need to edit certain opportunities related to the incoming accounts of a trigger, you would query for all the opportunities of all the accounts at the start of your code, and then loop over that list to sort them for each account.

I can't give you a more specific answer since we don't know your requirement, but that's the gist of the idea.

  • My requirement is i need to display related opportunities in lead page with field called sic code in lead page.For example called sic=123 in lead page.so,now i need to display opportunities with sic code like 123,1234 – Pavan tej Jun 20 '17 at 13:40

Not the answer you're looking for? Browse other questions tagged or ask your own question.