1

I am trying to query opportunities that have changed stages in a given time range. I have been trying to do it by using a subquery on the opportunityHistories and filtering based off of that, but I haven't been able to get it to work yet. I would like to use a query like:

Select id, name, quote_created__c, stagename 
from opportunity 
where (select stageName, 
              createddate, 
              systemmodstamp 
         from OpportunityHistories 
        where createddate > 2018-02-27T00:00:00.000Z 
        and createddate < 2018-02-27T19:05:00.000Z) != null

Whenever I run this it says

Unknown error parsing query.

Is there any way to check if a subquery returns results in a where clause? Or is there a better way of searching for opportunities that have changed stage in a time range?

0

You were close. You simply need to bind the OpportunityId in the field list, and you'll get the opportunities that match the sub-query; this is called a semi-join. You can read more in the SOQL reference guide.

Select id, name, quote_created__c, stagename 
from opportunity 
where id in (select OpportunityId
         from OpportunityHistory
        where createddate > 2018-02-27T00:00:00.000Z 
        and createddate < 2018-02-27T19:05:00.000Z)
  • That works! Now my only issue is that isn't the only condition I need to search against and I'm getting the error: Semi join sub selects are not allowed with the 'or' operator. Is there any way I can get around that without making two queries? The query I am trying to make is: Select id, name from opportunity where (id IN (select opportunityId from OpportunityHistory where createddate>2018-02-27T00:00:00.000Z and createddate<2018-02-27T19:05:00.000Z)) OR (quote_created__c > 2018-02-27T00:05:00.000Z and quote_created__c < 2018-02-27T00:06:00.000Z) – ed54 Feb 28 '18 at 22:48
  • @ed54 Select id, name from opportunity where id IN (select opportunityId from OpportunityHistory where (createddate>2018-02-27T00:00:00.000Z and createddate<2018-02-27T19:05:00.000Z) OR (opportunity.quote_created__c > 2018-02-27T00:05:00.000Z and opportunity.quote_created__c < 2018-02-27T00:06:00.000Z)) -- put the OR in the sub-query. – sfdcfox Feb 28 '18 at 22:54
0

If you can use apex, you can do this in two steps:

List<OpportunityHistory> histories = [select OpportunityId from OpportunityHistory 
          where createddate > 2018-02-27T00:00:00.000Z 
            and createddate < 2018-02-27T19:05:00.000Z];

Set<Id> theIds = new Set<Id>();
for (OpportunityHistory oh : histories) {
    theIds.add(oh.OpportunityId);
}

List<Opportunity> opptys = [select Id from Opportunity where Id in :theIds];

That will give you the opportunities that match the criteria you specified above

  • I can't use apex. I am building a separate API that needs to query our sfdc instance for these opportunities. – ed54 Feb 28 '18 at 22:49
  • Then it'll be 2 API calls, one for each query. You cannot get all that info in a single SoQL statement. – Sebastian Kessel Feb 28 '18 at 23:23

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.