3

Given Campaign Ids, List All Contacts Under Campaign that has some Opportunities associated. And I am using the below SOQL query for it. But it gets 'Nesting of semi-join sub-selects is not supported'. How do I get around that error? I know SOQL allows upward nesting and not downward. Here is my query below. Thanks !

SELECT contactid,contact.name,contact.account.name, role,opportunityid, opportunity.name
 FROM opportunitycontactrole
 WHERE contactid in (SELECT contactid FROM campaignmember WHERE campaignid in (SELECT id from campaign))

2 Answers 2

3

I don't understand the last part of your SOQL in which you are pointing the CampaingId to the Campaign object. Please, try this query instead:

List<OpportunityContactRole> ocr =[SELECT contactid,contact.name,contact.account.name,role,opportunityid opportunity.name
FROM opportunitycontactrole WHERE contactid in (SELECT contactid FROM campaignmember WHERE campaignid != null)];

It looks like you want to make sure that there is a campaign related to the OpportunityContactRole. By adding the !=null (not equal) we can accomplish that logic and avoid the error which it is a limitation in your SOQL.

UPDATE:

You can use a list of Campings and use it as your criteria as part of your SOQL.

List<Campaign> camList = [SELECT Id FROM Campaign]; 

List<OpportunityContactRole> ocr =[SELECT contactid, contact.name,  contact.account.name, role, opportunityid, opportunity.name
FROM opportunitycontactrole WHERE contactid in (SELECT contactid FROM campaignmember WHERE campaignid IN: camList)];

If you can pass the campaigns to thatcamListlist you could do something like the above query. You need to be aware of governor limits for the campaign list.

6
  • Thanks a lot for the above. We are trying to limit what campaigns to use and it is something decided dynamically via a dashboard platform 'Klipfolio'. Now it seems to we cannot use APEX there, so wanted a way to know how we can dynamically limit what campaigns to use, (this selection is passed from klipfolio in the form of variable) just need to understand how SOQL can use it. Mar 11, 2017 at 18:59
  • I see. You could use a list of Campaigns and use the list to define your query. Check that updated answer. Mar 11, 2017 at 19:12
  • Thank you for code ! But Klipfolio as a dashboard only supports integration with SOQL and not APEX code, so unfortunately that cannot be used unless there is some other way around. Thanks again ! Mar 11, 2017 at 19:26
  • What if you added something like where campaign__r.Name = 'xyz' in your filter criteria instead of the last query which is not allowed
    – Eric
    Mar 11, 2017 at 23:29
  • 1
    @AdrianLarson the following works for me Select Id From OpportunityContactRole Where ContactId In (Select Id From Contact Where Account.Name = 'test')
    – Eric
    Mar 12, 2017 at 1:12
3

If you can define the criteria for the account filter in your external application then you could do something like this:

Select Id 
   From OpportunityContactRole 
   Where ContactId In 
      (Select Id From Contact Where Account.Name = 'test')

You must log in to answer this question.

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