Table Mapping using joins

If more than one input table is included in a transform action, use the Table Mapping area to create join relationships between the fields in the tables.

  • Drag from a field in one table “A” to a field in another table “B.” This creates an initial relationship between the two fields, which by default is an Inner join, and it is indicated by a join line extending between the two fields. Once joined, the two fields are moved to the top of their respective table column lists.

  • To change the type of join, right-click on the join line, and select a different type -
    • Matching rows between <Table A> and <Table B>
    • An “Inner Join” that includes only matching records from Table A and Table B.
    • All rows from <Table A>
    • An “Outer Join” that includes all records from Table A and those records that match from Table B.
    • All rows from <Table B>
    • An “Outer Join” that includes all records from Table B and those records that match from Table A.
    • All rows from <Table A> and all rows from <Table B>
    • A “Full Outer Join” that includes both the non-matching and matching records from both tables.
  • Repeat for all fields being mapped between the two tables.

When creating multiple field mappings between two tables, all the mapping types must be the same; all mappings are changed to the type of the most recently configured mapping.

For some transform actions, you can join additional tables as necessary.

As you define your mapping, click the execution tree icon to view the joins in a tree-like structure showing the processing order to be used for generating SQL.

An ambiguous join configuration is one that may not produce the results you expect, depending on the order in which Data Guru decides to process the joins. An ambiguous join can occur only if at least three tables are included in the table mapping.

If one or more ambiguous joins are present, Data Guru flags the first one it detects with a warning. The mappings or set of mappings causing the ambiguous condition appear in yellow, accompanied by a small icon . Although you can save and execute the action, you should verify that it is producing the intended results when the "no matching rows" condition occurs.

Data Guru flags the following ambiguous join configurations:

  • A FULL OUTER JOIN (join type of All rows from <Table A> and all rows from <Table B>) is present, and one or more additional tables are joined in an action.

  • Two Conflicting Outer Joins exist, so that both of the following are true -
    • All rows from <Table A> joined to <X>
    • All rows from <Table B> joined to <X>

    where <X> is a single table or a group of tables.

    In this single table example, <X> is Table C.

    In this group (indirect) example, <X> is the table group consisting of Table C and Table D

To correct an ambiguous condition, right-click on one of the flagged joins, and select a different join type.

Whenever a field used in the configuration of an action is removed from the upstream table definition, it is important that the missing field is identified in the affected downstream actions. Data Guru uses special indicators when these types of changes exist in the schema, so that they can be addressed.

If the change occurs in a data transform action, the downstream actions affected by the missing field are identified in the macro or workflow as in the following example:

If you open the downstream action, missing fields appear in yellow:

At this point, you can correct the discrepancy, either by adding the missing field in the upstream action or by removing it from the downstream action.

Last modified: Thursday December 19, 2024

Is this useful?