+ Start a Discussion
ministe2003ministe2003 

SOQL no results - exception or no exception?!

Hello,

I'm struggling to come up with an answer to this by myself so thought Id throw it out there.

 

Sometimes if a SOQL query returns no results it throws an exception, which can be caught and then the field populated differently, ie created.  However sometimes it doesn't throw an exception so I have to wrap my query in a try{catch} and then outside of that, also check to make sure the return isn't null so I can handle that too.

 

Is there a logical reason why it does this?  It's making my code unnecessarily long and at best, is just annoying!

 

Thanks for any replies

Steven

Best Answer chosen by Admin (Salesforce Developers) 
bob_buzzardbob_buzzard

In the first instance you are assigning the results into a variable that holds a single record, which according to the docs only works if the query returns exactly one element.  Hence the exception.

 

In the latter, you are assigning the results to a list and its perfectly valid for a list to contain zero elements.

 

I'd say you have two choices:

 

1.  Alter the first piece of code so that you store the results in a list, then you will be checking in both cases if there are any returned values.

2. Alter the second piece of code to try to access the element at [0] of the list - this will throw an exception if the list is empty.

 

Choice 1 is the correct one IMHO, as otherwise you're using exception handling for business logic.

All Answers

bob_buzzardbob_buzzard

I wouldn't expect a SOQL query that returns no results to throw an exception - it sounds more likely to be how you are using the results - e.g. appending [0] to the query to retrieve the first result will throw an exception if there are no results.  

 

Do you have an example piece of code that throws an exception?

ministe2003ministe2003

This section works as the assignment from the query returns this exception:

System.QueryException: List has no rows for assignment to SObject

 

try{
bpReg2 = [SELECT Id, BpRegHeader__c, GDP__c, IWB__c, LRS__c, No_classrooms__c, Other1_IWB_Share__c, Other1_LRS_Share__c, Other2_IWB_Share__c, Other2_LRS_Share__c, Population__c, Prom_IWB_Share__c, Prom_LRS_Share__c, Smart_LRS_Share__c, Smart_IWB_Share__c
          FROM BpRegSec2__c
          WHERE BpRegHeader__r.Account__c = :AccountID
          LIMIT 1];
}catch(Exception e){
    bpReg2 = new BpRegSec2__c();
    bpReg2.BpRegHeader__c = bpRegHeader.Id;
}

 

However this block, right below the block you've just seen, doesnt return any value but doesnt throw the exception:

 

List<BpRegForecast__c> fcast = [SELECT Id, IWB_Rev__c, IWB_Units__c, LRS_Rev__c, LRS_Units__c, Other_Rev__c, Total_Rev__c, Quarter__c
			FROM BpRegForecast__c
			WHERE BpRegSec2__c = :bpReg2.Id];

Below this I have to check the return manually.

 

 

bob_buzzardbob_buzzard

In the first instance you are assigning the results into a variable that holds a single record, which according to the docs only works if the query returns exactly one element.  Hence the exception.

 

In the latter, you are assigning the results to a list and its perfectly valid for a list to contain zero elements.

 

I'd say you have two choices:

 

1.  Alter the first piece of code so that you store the results in a list, then you will be checking in both cases if there are any returned values.

2. Alter the second piece of code to try to access the element at [0] of the list - this will throw an exception if the list is empty.

 

Choice 1 is the correct one IMHO, as otherwise you're using exception handling for business logic.

This was selected as the best answer
ministe2003ministe2003

OK thanks, so its a simple case of the variables I assign it to then.  I understand now, before I was just guessing!

Thanks!

Shiva RajendranShiva Rajendran
Hi ministe ,
For a bit more clarity on your question asked
Suppose you have a query like below 
List<Account> accounts1=[select id from account where id='0016F00001ws44D'];
Account acc=[select id from account where id='0016F00001ws44D'];

In this case query1 won't throw an exception if the return is null, while query 2 will throw an exception.
Also if you accessed accounts1[0] ,it will also throw an exception.
Thanks and Regards,
Shiva RV
ministe2003ministe2003
Thanks, but this is over 6 years old and was answered on the day it was asked!