August 15, 2023

How to set schema for generating the child-to-parent relationship query – Docs for ESB 6.x

How to set schema for generating the child-to-parent relationship query

This section demonstrates how to set the module name and schema columns for
generating a child-to-parent relationship query.

The following two child-to-parent relationship query examples will be used for
demonstration purpose in the following steps.

  • SELECT Name, Account.Name, Account.Owner.Name FROM Contact, a
    child-to-parent relationship query with standard object and fields, and

  • SELECT Id, FirstName__c, MotherOfDaughter__r.FirstName__c FROM
    Daughter__c
    , a child-to-parent 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
    .

  1. Set the module name with the name of the object specified in the
    FROM clause, Contact and
    Daughter__c in above examples.
  2. Create a column for each field in the field list (separated by commas) after
    SELECT in the schema dialog box.

    For the first example, you need to create three columns for the three fields
    Name, Account.Name, and
    Account.Owner.Name.

    For the second example, you need to create three columns for the three fields
    Id, FirstName__c, and
    MotherOfDaughter__r.FirstName__c.

  3. 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 names of the three columns are set to
    Name, Account_Name, and
    Account_Owner_Name.

    For the second example, the names of the three columns are set to
    Id, FirstName__c, and
    MotherOfDaughter__r_FirstName__c.

  4. Set the type of each column.

    The schema for the first example should be set like this:

    child_to_parent_query_schema.png

    And the schema for the second example should be set like this:

    child_to_parent_query_schema_custom.png

    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.


Document get from Talend https://help.talend.com
Thank you for watching.
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x