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 __r
instead of __c. For more information, see Understanding Relationship Names, Custom
Objects, and Custom Fields.
Procedure
-
Set the module name with the name of the object specified in the outer query
FROM clause, Account and
Mother__c in above examples.
-
Create a column for each field (inclulding the fields in subquery) after
SELECT in the schema dialog box.
For the first example, you need to create three columns for the three fields,
including two fields Name and Owner.Name
after the outer SELECT, and one field
CreatedBy.Name after the subquery
SELECT.
For the second example, you need to create two columns for the two fields,
including the field LastName__c after the outer
SELECT, and the field FirstName__c
after the subquery SELECT.
-
For the fields in the outer
SELECT clause, 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 SELECT clause 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>_records_<$YYY>, where
<$XXX> corresponds to the name of the object specified
in the subquery FROM clause, and <$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__c in the query, you need to
replace '_' in the generated query with '.' manually.
