function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
RepsGRepsG 

SOQL Nested queries Problem

Hi, 

 

I have the following soql query:

 

List<CampaignMember> cmList =

[

select Id, ContactId, CampaignId from CampaignMember

where ContactId =: ContactId
AND
CampaignId IN (select Id from Campaign where isActive = true AND product__c =:oProduct AND source__c includes (:omailStype))
];

 

The result i get from the query is null. The issue with the soql above is the nested query:

(select Id from Campaign where isActive = true AND product__c =:oProduct AND source__c includes (:omailStype))

 

when I remove the 'AND product =: oProduct' it works fine, but when I include this text it stop working. I know 100% that couple of campaigns with the correct product & source value exists. 

 

Can anybody help where I may going wrong with SOQL query?

 

Thanks inadvance

bob_buzzardbob_buzzard

What value do you have for oProduct?

RepsGRepsG

Thanks Bob, for replying. I managed to figure out the problem.

 

The problem (and I not to happy to admit this) was schoolboy error. I forgot to add the contact to the campaign. Hence why it was not returning a value. 

 

Howevery I have encountered another little issue which i think you may help me with. 

 

In my nested query I cannot seem to order by created date. Is this possible? Everytime I try to save the following query

 

 

[select .... from CampaignMember

where Contactid =: ContactId

AND

CampaignId IN (select Id from Campaign where isActive = true AND product__c =:oProduct AND source__c includes (:omailStype) order by CreatedDate desc)

];

 

It throw an error: expecting a right parentheses, found 'order'. 

 

Thanks in advance. 

bob_buzzardbob_buzzard

I'm not suprised that you can't do that - your subquery is used to get campaign ids to check against the main query, so ordering them doesn't make sense - should your order by be outside the subquery?

RepsGRepsG

That makes sense but how do i order by the campaign created date (not campaignmemeber created date) outside the sub query? 

bob_buzzardbob_buzzard

You can only do that if the campaign created date is available in your main query - you could put a formula field on the campaign member to pull that in to the record.  I don't think you can do this in SOQL as you can't have multiple sub queries.

asish1989asish1989

HI

I think binding is not possible in Includes statement . you are binding source__c with some varible source__c includes (:omailStype), here is link you can refer 

 

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_variables.htm

 

Bind expressions can't be used with other clauses, such as INCLUDES.

 

Now How to write OrderBy outside subquery

 

[select .... from CampaignMember

where Contactid =: ContactId

AND

CampaignId IN (select Id from Campaign where isActive = true AND product__c =:oProduct AND source__c includes (:omailStype) )order by CampaignId.CreatedDate desc];

 

If this post answers your questions please mark it as solved and give kudos If this helps you.

 

Thanks

Asish