Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Kamran-RaoKamran-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

Message Edited by Kamran-Rao on 07-30-2009 04:09 AM
Best Answer chosen by Admin (Salesforce Developers) 
Kamran-RaoKamran-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

Message Edited by Kamran-Rao on 07-31-2009 02:42 AM

All Answers

jkucerajkucera

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];

 

 
Kamran-RaoKamran-Rao

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

Message Edited by Kamran-Rao on 07-30-2009 03:55 AM
Message Edited by Kamran-Rao on 07-30-2009 04:05 AM
jkucerajkucera

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.
 


Kamran-RaoKamran-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
Message Edited by Kamran-Rao on  07-31-2009 02:42 AM
This was selected as the best answer