Help get this topic noticed by sharing it on Twitter, Facebook, or email.

Subquery within a OpenQuery

I am trying to insert records from salesforce to my local table using DBAmp. The query I am using is
INSERT INTO A_AccountIds(Account_Id,Account_Name) SELECT * FROM OpenQuery(SALESFORCE, 'SELECT JVCO_Customer_Account__c,JVCO_Customer_Account__r.Name FROM Account where JVCO_Customer_Account__r.Id in (SELECT distinct OldAccountId from A_AffiliationMapping)')
My intent is to populate A_AccountId table with data from salesforce where the condition is a Id from my another local table which is A_Affiliation mapping
The A_AffiliationMapping and A_AccountIds are SQL tables on my local machine. when I execute this I see the following error
OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE" returned message "Error: MALFORMED_QUERY:
JVCO_Customer_Account__r.Id in (SELECT distinct OldAccountId from A_AffiliationMapping)
^
ERROR at Row:1:Column:129
unexpected token: OldAccountId".
Msg 7320, Level 16, State 2, Line 41
Cannot execute the query "SELECT JVCO_Customer_Account__c,JVCO_Customer_Account__r.Name FROM Account where JVCO_Customer_Account__r.Id in (SELECT distinct OldAccountId from A_AffiliationMapping)" against OLE DB provider "DBAmp.DBAmp" for linked server "SALESFORCE".

Any help around this is highly appreciated
1 person has
this question
+1
Reply