5

I have 2 queries in my code and result of 1st is used in 2nd query as below:

External_System_Mapping__c[] LeadProgram = [
    SELECT Salesforce_Value__c 
    FROM External_System_Mapping__c
    WHERE Mapping_Type__c = 'Program' 
    AND External_System_Value__c =:application_program
];

List<Lead> matchingLeadsListWIProgram = [
    Select Id, Status from Lead
    where isConverted = false
    and ((Program__c =: LeadProgram[0].Salesforce_Value__c)
    and (
        (Email != null AND Email = :application_email)
        OR (MobilePhone != null AND MobilePhone = :application_mobile)
        OR (Email != null AND Email=:spjat_email)
        OR (MobilePhone != null AND MobilePhone =:spjat_mobile)
    ))
];

But sometimes 1st query is returning null and I get this error:

List index out of bounds: 0

Any suggestions to avoid this error?

9

It is failing when you try to access the Salesforce_Value__c from the first record in your list of External System Mapping (LeadProgram[0].Salesforce_Value__c). You can check the size of the list before using it in the second query:

External_System_Mapping__c[] LeadProgram = [Select Salesforce_Value__c from External_System_Mapping__c Where Mapping_Type__c = 'Program' and External_System_Value__c =:application_program ];

if(LeadProgram.size() > 0) {
    List<Lead> matchingLeadsListWIProgram = [Select Id, Status from Lead where isConverted = false and ((Program__c =: LeadProgram[0].Salesforce_Value__c) and ( (Email != null AND Email = :application_email) OR (MobilePhone != null AND MobilePhone = :application_mobile) OR (Email != null AND Email=:spjat_email) OR (MobilePhone != null AND MobilePhone =:spjat_mobile))) ];
}
  • 3
    The best way to check if list size > 0 is to check with !List.isEmpty() . This would faster the apex performance. – NoNicknameSFDC Feb 7 '17 at 11:19
5

You'll need to account for situations where the list is empty by checking it's size before executing the second query. like this:

External_System_Mapping__c[] LeadProgram = [Select Salesforce_Value__c 
from External_System_Mapping__c Where Mapping_Type__c = 'Program' 
and External_System_Value__c =:application_program ];


if (leadProgram.size() > 0) {
 List<Lead> matchingLeadsListWIProgram = [Select Id, Status from Lead where isConverted = false and ((Program__c =: LeadProgram[0].Salesforce_Value__c) and ( (Email != null AND Email = :application_email) OR (MobilePhone != null 
AND MobilePhone = :application_mobile) OR (Email != null 
AND Email=:spjat_email) OR (MobilePhone != null AND MobilePhone =:spjat_mobile))) ];
}
  • 3
    in this case when no rows are found no exception will be thrown because the assignment is to a List of that object type – Eric Feb 6 '17 at 16:44
  • 2
    In addition to this approach not exhibiting the correct behavior, I believe it is less efficient to catch a QueryException than to check for an empty list. My personal opinion is that exceptions should mostly be spared for when you can't avoid them beforehand. – Adrian Larson Feb 6 '17 at 16:49
  • 4
    The try-catch is indeed useless. Also, the result of a query is never null. – sfdcfox Feb 6 '17 at 16:58
1

The reason for the error was already addressed but I noticed few issues in your query.

1) Having a null check in the second query will prevent Salesforce from using Index on that field. If null check is needed you can do the check on the binding value for the same. Please check the Salesforce large data volume best practice.

its better to write like.

List<Lead> matchingLeadsListWIProgram = [
    Select Id, Status from Lead
    where isConverted = false
    and ((Program__c =: LeadProgram[0].Salesforce_Value__c)
    and (
        (Email = :application_email)
        OR (MobilePhone = :application_mobile)
        OR (Email=:spjat_email)
        OR (MobilePhone =:spjat_mobile)
    ))
];

2) I am not sure whether Program__c is indexed or not. if not, the query will suffer because the second part of your query is not selective either. Email field is indexed on Lead but not the MobilePhone field. Because of the OR condition, the query will cause a table scan on lead (*Unless sharing is restricting the record visibility). Its better you leverage SOSL instead of SOQL for this.

Check the query plan in the developer console for details on the query execution.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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