This section demonstrates how to set the module name and schema columns for generating a parent-to-child relationship query.
The following two parent-to-child relationship query examples will be used for demonstration purpose in the following steps.
SELECT Name, Owner.Name (SELECT CreatedBy.Name FROM Notes) FROM Account, a parent-to-child relationship query with standard object and fields, and
SELECT LastName__c, (SELECT FirstName__c FROM Daughters__r) FROM Mother__c, a parent-to-child relationship query with custom object and fields.
Note that here you must use the relationship name with
__c. For more information, see Understanding Relationship Names, Custom Objects, and Custom Fields.
Set the module name with the name of the object specified in the outer query
Mother__cin above examples.
Create a column for each field (inclulding the fields in subquery) after
SELECTin the schema dialog box.
For the first example, you need to create three columns for the three fields, including two fields
Owner.Nameafter the outer
SELECT, and one field
CreatedBy.Nameafter the subquery
For the second example, you need to create two columns for the two fields, including the field
LastName__cafter the outer
SELECT, and the field
FirstName__cafter the subquery
For the fields in the outer
SELECTclause, which are outside parentheses, set the name of each column with the name of each field and replace all dots in the column name with underscore characters.
For the first example, the column names for the two fields Name and Owner.Name in the outer
SELECTclause are set to Name and Owner_Name.
For the second example, there is no dot in the field name, so the column name is same as the field name.
For the fields in the subquery
SELECT, construct the column names using the pattern
<$XXX>corresponds to the name of the object specified in the subquery
<$YYY>will be the field name with all dots replaced by underscore characters.
For the first example, the column name for the field CreatedBy.Name in the subquery is set to Notes_records_CreatedBy_Name.
For the second example, the column name for the field FirstName__c in the subquery is set to Daughters__r_records_FirstName__c.
Set the type of each column.
The schema for the first example should be set like this:
And the schema for the second example should be set like this:
Note that the underscore character '_' is used as a separator between the relationship name and the field name in Talend schema, so only the underscore character '_' that goes after '__r' or '__c' will be replaced by the dot character '.' when generating the query. If the underscore character '_' is a part of any custom name in the schema, for example,
Contact_custom_field__c, which should be
Contact.custom_field__cin the query, you need to replace '_' in the generated query with '.' manually.