+ Start a Discussion
Swami DeshanelSwami Deshanel 

I am getting the error: Non-selective query against large object type (more than 100000 rows).

Here is my Code.   The error occurs when my users convert a lead without creating an opportuntity.


trigger checkPrimaryCampaignSource on Lead (after update) {
    //This will be a set of all opportunity IDs for leads that were just converted
    Set<Id> convertedOppIds = new Set<Id>();
    for (Lead l :Trigger.new) {
        if (l.IsConverted == true) {
            convertedOppIds.add(l.ConvertedOpportunityId);
        }
    }
  
    //Now get a set of all of the campaign members related to the converted leads who have not responded to any of the campaigns
    List<CampaignMember> campaignMembers = [SELECT Id, Lead.ConvertedOpportunityId FROM CampaignMember WHERE Lead.ConvertedOpportunityId IN :convertedOppIds AND HasResponded = false];
    //Construct a set of opportunity ids that were converted from leads who did not respond to the campaign
    Set<Id> nonResponsiveOppIds = new Set<Id>();
    for (CampaignMember cm : campaignMembers) {
        nonResponsiveOppIds.add(cm.Lead.ConvertedOpportunityId);
    }
  
    //Now we can get a list of all of the opportunities where we need to remove the primary campaign source
    List<Opportunity> oppsToUpdate = [SELECT CampaignId FROM Opportunity WHERE Id IN :nonResponsiveOppIds];
    for (Opportunity opp : oppsToUpdate) {
        opp.CampaignId = null;
    }
  
    if (oppsToUpdate.size() > 0) {
        update oppsToUpdate;
    }
}
KaranrajKaranraj
Hi Sam,

The reason for this error message is when you are converting the lead without opportunity your Set variable will be "convertedOppIds" will be null. When the "convertedOppids" is null then in the following line, it will fetch all the CapaignMemebers in the your org, so that it will hit the salesforce governor limit. 

//Now get a set of all of the campaign members related to the converted leads who have not responded to any of the campaigns
    List<CampaignMember> campaignMembers = [SELECT Id, Lead.ConvertedOpportunityId FROM CampaignMember WHERE Lead.ConvertedOpportunityId IN :convertedOppIds AND HasResponded = false];

To avoid this, just modify the first if condition in your for loop,
if (l.IsConverted == true && l.ConvertedOpportunityId != null) {
            convertedOppIds.add(l.ConvertedOpportunityId);
  }

I believe this trigger logic written for only when your lead is converted with the opportunity. Hope this will solve your issue.

Thanks,
Karan

Pramod_SFDCPramod_SFDC
Hi ,

I hope, you add in more filter for the query and make it selective. Below provided link will help you in making changes to the query.

>> https://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_VLSQ.htm


Regards
Pramod
David "w00t!" LiuDavid "w00t!" Liu
You basically need to add a WHERE clause to your SOQL query that filters on one of these fields:
http://salesforce.stackexchange.com/questions/218/what-standard-and-custom-fields-are-indexed

If you can't filter on those fields, you need to set a field that you can filter on to be an "External ID" - you can do this be editing the field's properties.