August 15, 2023

Configuring data mapping to generate a subquery – Docs for ESB 6.x

  2. Talend for ESB Docs
  3. Docs 6.x
  4. Configuring data mapping to generate a subquery – Docs for ESB 6.x

Configuring data mapping to generate a subquery

  1. Click ELTSubqueryMap to open its Basic settings view.

    components-teltteradatamap_s1_eltsubquerymap.png

    Note that you do not need to specify the Teradata database connection
    information in the ELTSubqueryMap component.
    The connection information will be specified in the ELTMap component.
  2. Click the […] button next to ELT Teradata Map Editor to open its map
    editor.

    components-teltteradatamap_s1_eltsubquerymap_mapeditor.png

  3. Add the input table CourseScore by clicking
    the [+] button in the upper left corner of the
    map editor and then selecting the relevant table name from the drop-down list in
    the pop-up dialog box.
  4. Add an output table by clicking the [+]
    button in the upper right corner of the map editor and then entering the table
    name TotalScore in the corresponding field in
    the pop-up dialog box.
  5. Drag StuName, Subject, and Score columns in
    the input table and then drop them to the output table.
  6. Click the Add filter row button in the upper
    right corner of the output table and select Add an
    other(GROUP…) clause     from the pop-up menu. Then in the
    Additional other clauses (GROUP/ORDER
    BY…)     field displayed, enter the clause GROUP BY CourseScore.StuName, CourseScore.Subject.

    Add the aggregate function SUM for the column Score of the output table by changing the expression of this
    column to SUM(CourseScore.Score).
  7. Click the Generated SQL Select query for “table1”
    output     tab at the bottom of the map editor to display the
    corresponding generated SQL statement.

    components-teltteradatamap_s1_eltsubquerymap_sql.png

    This SQL query will appear as a subquery in the SQL query generated by the
    ELTMap component.
  8. Click OK to validate these changes and close
    the map editor.
  9. Connect ELTSubqueryMap to ELTMap using the Link > TotalScore (table1)
    link. Note that the link is renamed automatically to TotalScore (Table_ref) since the output table TotalScore here is a reference table.
Document get from Talend https://help.talend.com
Thank you for watching.
Docs 6.x
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x