I have a very bizarre issue with a batch job that is using the following query as a scope to generate new records

Select id, Management_Fee__c, Payment_Schedule_Comments__c, Payment_Schedule_Start_Date__c, Site__c, Name, Revenue_Share__c, Revenue_Share_Notes__c, Value__c, Contract_Status__c, Store_Refit__c, Price_Change_Reason_Code__c, Contract_end_reason_code__c 
from TI_Contract__c 
where Contract_Status__c = 'Live' OR 
   (Contract_Status__c = 'Dead' AND Contract_End_Date__c = THIS_QUARTER)

for some reason, a small bunch of records with the Contract_Status set to 'Live' are omitted from the result, however, when I remove (Contract_Status__c = 'Dead' AND Contract_End_Date__c = THIS_QUARTER) they show up. I run the full query in the dev console against live data and it produces the right scope but does not work in APEX.

Can anyone explain why this is happening? I am going nuts.

Many thanks in advance!

@DavidReed and Cropredy, thanks. I confirm that I am using the same batch with different queries in the same user context. The batch class has access to all records and I was comparing it to the DC and results of these three 'where' clauses used in the batch:

'where Contract_Status__c = \'Live\' OR (Contract_Status__c = \'Dead\' AND Contract_End_Date__c = THIS_QUARTER)';
'where Contract_Status__c = \'Live\' OR (Contract_Status__c = \'Dead\')';       
'where (Contract_Status__c = \'Live\' OR Contract_End_Date__c = THIS_QUARTER) and name = \'24087\'';  // targeting a particular record without result

the one that brings up the right amount of 'live' record is this:

'where Contract_Status__c = \'Live\''; 

but then I am missing out 'Dead' contract. I can't understand why records with 'Live' status would be affected.

Is it worth mentioning that Contract_Status__c is a picklist?

  • 1
    To confirm, are you finding this result after testing the same batch class (with different queries) in the same user context, or are you comparing the batch query with results from the Developer Console? It's very easy to get tripped up with different user contexts while testing Asynchronous Apex.
    – David Reed
    May 5, 2020 at 23:32
  • 1
    does the running user for the batch class have access (assuming with sharing) to the relevant records? When you use DC as sysad, you'll see all recs
    – cropredy
    May 5, 2020 at 23:40

1 Answer 1


Any luck troubleshoot with the following steps?

  1. In your APEX, remove Contract_Status__c = \'Live\'. since this condition is surely working.
  2. temporarily hardcoding a date instead of Date Literal where you expect the record to return. E.g. in the Where Clouse. Contract_End_Date__c = 1999-01-01 if your record End date is 1990-01-01. Let's confirm whether this returns your data at the first place.

Alternately using condition Contract_Status__c = \'Live\', try System.debug and observe what value is returned in Contract_End_Date__c field. This is to verify whether it matches range in THIS_QUARTER literal.

It should reveal more unknown to your issue.

  • I have gone as far as reducing the statement to where Contract_Status__c = \'Live\' OR Contract_Status__c = \'Dead\' and now it is missing many more records, including those that were generated before.
    – wmin
    May 6, 2020 at 13:46
  • Sounds very likely as running user contexts issue. Or it could be an overlook that values contains trailing spaces. E.G 'Dead ' instead of 'Dead' stored.
    – EugeneO
    May 6, 2020 at 14:04
  • the Contract Status is a dropdown value and is definitely not the issue with trailing spaces. Interestingly the 'Dead' OR 'Live' statement has reduced the number of generated records from over 3K to 329. I am the system admin user and don't normally have problems with permissions to access or execute. I am curious to know what you mean by the contexts issues. Thanks for your responses,
    – wmin
    May 6, 2020 at 14:20

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .