We are trying to pull few lookup fields by putting query in Advanced option of SalesForce Query component but facing error as concerned field is not available in queried table.
For example -
Select abc_aa__c , abc_bb__r.abc_bb__c from xyz
Here we are facing issue with the second field that is being looked up from another table which has reference with table xyz.
Is anyone else seeing the same issue?
Hi @SS146107
Welcome to the Matillion ETL Community! this is a really good question, I am sure the community users will be over soon to share their thoughts on this.
In the meantime I wanted to share what I have seen some users do in relation to similar.
Some users have been known to load the columns they need from each table separately including the keys linking them. They have then joined them in the warehouse using a Matillion transformation job.
That is one potential option but I am sure our awesome users can offer some more insight on this.
Kind regards, Joe
You can execute SOQL query with look up fields using Matillion Salesforce Query component Advanced mode but need to add a parameter (QueryPassthrough=true) to Connection Options.