tSalesforce_Input - Returning NULL values - I have amended schema

Five Stars

tSalesforce_Input - Returning NULL values - I have amended schema

Hi,
I am attempting to use a manual SOQL query from a tSalesforce_Input component:
"select 
Account.LLC_BI__lookupKey__c, 
Account.ID
,(Select Contact.LLC_BI__Home_City__c
,Contact.Home_Country__c
,Contact.LLC_BI__Home_Address__c
,Contact.LLC_BI__Home_Zipcode__c from Account.Contacts)
 from Account"
Which seems to run correctly - however, i get NULL results on all the columns from the 'Contact' module.
I have read a lot of information about amending the schema for the linked module (i.e. nameofcurrentmodule_nameoflinkedmodule_nameofcolumn) and so my schema looks like this:
LLC_BI__lookupKey__c
Id
Account_Contact_LLC_BI__Home_City__c
Account_Contact_Home_Country__c
Account_Contact_LLC_BI__Home_Address__c
Account_Contact_LLC_BI__Home_Zipcode__c

Can anyone help me understand why my fields from the contact module returns only NULL Values?

Many thanks!

Five Stars

Re: tSalesforce_Input - Returning NULL values - I have amended schema

I've also tried approaching this as a child -> parent query and still get the same result.

The new query:

"Select Contact.AccountId
,Contact.Account.LLC_BI__lookupKey__c
, Contact.LLC_BI__Home_City__c
,Contact.Home_Country__c
,Contact.LLC_BI__Home_Address__c
,Contact.LLC_BI__Home_Zipcode__c from Contact"

Schema:

AccountId
Contact_Account_LLC_BI__lookupKey__c
LLC_BI__Home_City__c
Home_Country__c
LLC_BI__Home_Address__c
LLC_BI__Home_Zipcode__c

I get the results from the Contact module, but NULLs from Account?

Please can anyone help me?
One Star

Re: tSalesforce_Input - Returning NULL values - I have amended schema

Hi,
There is some limitations when working with custom SOQL queries into TOS.
Maybe this post could help you .
Also, the schema fields and Salesforce fields mus have the same name or you'll get a NULL value (in fact schema's field with a name different of the Salesforce fields are not populated and remain null). 
Hope this helps.
Five Stars

Re: tSalesforce_Input - Returning NULL values - I have amended schema

@TRF - Thank you - I am aware of the limitations particularly relating to the schema, but my queries aren't particularly complex, and I have followed the user guide on how to edit the schema with the module name and columns etc. but it still returns only NULL values.

Can anyone suggest where I have gone wrong?
One Star

Re: tSalesforce_Input - Returning NULL values - I have amended schema

@monkeyb33f - can you share the link to the user guide you mentioned in your last post?
Regards,
FT
Five Stars

Re: tSalesforce_Input - Returning NULL values - I have amended schema

@ TRF - Sure. 

The link for the query i used was:
[font=Calibri, sans-serif]https://developer.salesforce.com/blogs/developer-relations/2013/05/basic-soql-relationship-queries.h...[/font]


The link for the schema guide was:
https://jira.talendforge.org/browse/TDI-6612



Many thanks!
Five Stars

Re: tSalesforce_Input - Returning NULL values - I have amended schema

I think from finding some old forum posts, Talend just doesn't support Parent to Child relationships in SOQL.

I can get a child to parent working.

Pretty disappointing if this functionality isn't in place.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog