5.2.2.1.2.3 Add Relation Expression Area

After selecting the source dataset, specify the Join relation expression. A Join relation expression can include any of the following components:

• And or Or option: Use to specify whether a Join condition is required (And) or is an alternative (Or) to the previous expression.
• Not option: Use this option to negate a Join relation expression.
• Attribute 1: Contains the list of attributes from the dataset on the left of the relation expression.
• Condition: Shows the type of operator used for comparing Join fields.
• Attribute 2: Contains the list of attributes from the dataset on the right of the relation expression.
• Add: Adds the Join relation expression to the Relations area. Click Ok to save the Join transform and return to the DataSet Editor.

To join datasets, follow these steps:

1. Click the Join DataSet icon in the Transforms area.

   The system adds the icon to the first available cell in the grid area.

2. Link the DataSet icons to the Join icon.

   A blue arrow displays between joined transforms to show they are linked (refer to Transforms Usage Tips, for more information).

3. Double-click the Join icon.

   The Edit Join dialog box displays with each dataset. A red X displays above each dataset to indicate that you have not established the Join between datasets.

4. Define the join, within the Relations area, between the datasets (refer to Joining DataSets, for more information).
5. Click OK.
6. Repeat the procedure to build the remaining Join.

You may add more datasets to the Join at any time before saving the dataset. When you add a new dataset, however, the Join relation expressions between the original source datasets no longer exist. You must therefore redefine the joins between all the source datasets. When you access the Edit Join dialog box after adding another source dataset, the newly added dataset appears at the bottom of the Sources tree. The last two datasets always appear together on one level.

To define the Join between datasets (this procedure is a subset of joining the datasets procedure), follow these steps:

1. Select an attribute from the drop-down list under Attribute 1 in the Add relation expression area.
2. Select the appropriate Condition operator after selecting the first attribute. With Inner joins, you typically use all operators, however, use only the equal (=) condition operator with any Outer joins.
3. Select an attribute from the drop-down list under Attribute 2 after selecting the appropriate condition operator.
4. Do one of the following:
   1. Click Add after selecting the second attribute. The Join relation expression displays in the Relations area

      The red X no longer appears in the sources diagram since a Join was defined for these datasets.

   2. Click Delete to remove selected join relation expressions.
   3. Click Clear to clear all the join relation expressions established for joins on the two datasets.
5. Do one of the following:
   1. Select the And option to join the identified datasets where the first Join relation expression and the next apply.
   2. Select the Or option to join the identified datasets where the first Join expression or the next applies.
   3. Select the Not check box to negate a Join relation expression.

      Optional: Add any comments about defining a Join to the Comment area.

6. Click Ok to accept the joined relation after completing the Join and return to the DataSet Editor with the established dataset Joins.

   Note:

   Clicking Ok before creating join relation expressions for all of the source datasets displays a warning message.