Apex Code Development You need to sign in to do that
Don't have an account?
Kamran-Rao SOQL Subquery Issue -- Cannot use a foreign key for outer field for semi join at line
I have a custom object Media_Campaign__c which is carrying the OpportunityID of its parent Opportunity. Multiple MediaCampaigns can exist for a single opportunity. Also in my Contract, there is a field Opportunity_Id__c that carries the Opportunity ID of the Opportunity respective to that Contract.
My simple objective is to get only those rows from Media_Campaign__c whose corresponding opportunities have been converted into Contracts in a given dates.
What I do is, get Opportunity_Id__c from Contract between mentioned date like following:
select Opportunity_Id__c FROM CONTRACT WHERE CreatedDate > 2009-04-21T00:00:00.000Z --Works Fine
Then I use above query (it returns me Opp Ids against those contracts that have been converted to contract in a given time) as criteria in main query as following
select id, Opportunity__c from Media_Campaign__c
Where Opportunity__c in (select Opportunity_Id__c
FROM CONTRACT WHERE CreatedDate > 2009-04-21T00:00:00.000Z)
I am getting this error when I write the following query:
Cannot use a foreign key for outer field for semi join at line
Can somebody help me in reactifying this query. It is pretty simple in Oracle and SQLServer. But I am tired off trying in SOQL.
Rao
Kucera,
Thanks for your valued advice, I am unable to get the ID type of array this way as well.
However the 2nd trick, looping one, really worked for me since I have to get the contracts in a from a search criteria and I have resistricted the user to search only a month ranged contracts.
List <Contract> tempCon = [select Opportunity_Id__c FROM CONTRACT
WHERE CreatedDate > :searchObjectList[0].StartDate__c];
String[] contractIDs = new string[tempCon.size()];
for (integer i=0; i<tempCon.size(); i++)
{
contractIDs[i] = tempCon[i].Opportunity_Id__c;
}
Opportunity[] oppCampProd = [Select Id, Name, Opportunity__c
FROM Opportunity_Campaign_Products__c
Where Opportunity__c in : contractIDs];
Thanks,
Rao
All Answers
Not my area of expertise, but have you tried separating into 2 queries:
ID[] opptIDs=[select Opportunity_Id__c FROM CONTRACT WHERE CreatedDate > 2009-04-21T00:00:00.000Z];
Media_Campaign__c[] MCs=[select id, Opportunity__c from Media_Campaign__c
Where Opportunity__c in : opptyIDs];
Kucera,
Thank you for your response. I have tried it after your suggestion but it does not help.
Following simply doesn't work, compile time error:
ID[] opptIDs = [select Opportunity_Id__c FROM CONTRACT WHERE CreatedDate > 2009-04-21T00:00:00.000Z];
I have to change above like this:
Contract[] opptIDs = [select Opportunity_Id__c FROM CONTRACT WHERE CreatedDate > 2009-04-21T00:00:00.000Z]
Then puting it the following way:
List <Opportunity_Campaign_Products__c> oppCampProd = new List <Opportunity_Campaign_Products__c>();
oppCampProd = [Select Id, Name, Opportunity__c
FROM Opportunity_Campaign_Products__c
Where Opportunity__c in : oppIDs];
Gives me the error:
Invalid bind expression type of SOBJECT:Contract does not match domain of foreign key at line 317 column 50
Regards,
Rao
Sorry about that - bad syntax on my part.
What about:
ID[] opptyIDs = [select Opportunity_Id__c FROM CONTRACT WHERE CreatedDate > 2009-04-21T00:00:00.000Z].Opportunity_Id;
I haven't had time to check, but I believe this syntax should work to store an array of the ID's, which eliminates any foreign key problems as it's a dumb array.
If the above doesn't work, you could do the really long innefficent loop way:
List<ID> opptyIDs= new List<ID>();
Contract[] c= [select Opportunity_Id__c FROM CONTRACT WHERE CreatedDate > 2009-04-21T00:00:00.000Z]
for(Contract eachContract:c){
opptyIDs.add(eachContract.Opportunity_Id__c);
}
Opportunity[] oppCampProd = [Select Id, Name, Opportunity__c
FROM Opportunity_Campaign_Products__c
Where Opportunity__c in : opptyIDs];
Note that lists have a limit of 1000 elements if you go this way, so it may not scale for you.
Kucera,
Thanks for your valued advice, I am unable to get the ID type of array this way as well.
However the 2nd trick, looping one, really worked for me since I have to get the contracts in a from a search criteria and I have resistricted the user to search only a month ranged contracts.
List <Contract> tempCon = [select Opportunity_Id__c FROM CONTRACT
WHERE CreatedDate > :searchObjectList[0].StartDate__c];
String[] contractIDs = new string[tempCon.size()];
for (integer i=0; i<tempCon.size(); i++)
{
contractIDs[i] = tempCon[i].Opportunity_Id__c;
}
Opportunity[] oppCampProd = [Select Id, Name, Opportunity__c
FROM Opportunity_Campaign_Products__c
Where Opportunity__c in : contractIDs];
Thanks,
Rao