You need to sign in to do that
Don't have an account?
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
What value do you have for oProduct?
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.
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?
That makes sense but how do i order by the campaign created date (not campaignmemeber created date) outside the sub query?
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.
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