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?

2 Answers 2

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)
2
  • 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, 2018 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, 2018 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

2
  • I can't use apex. I am building a separate API that needs to query our sfdc instance for these opportunities.
    – ed54
    Feb 28, 2018 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. Feb 28, 2018 at 23:23

You must log in to answer this question.

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