Joining Data Sources

How to:

Reference:

If you select more than one data source for your data flow, you must use a join object to connect them.

You need to specify the common column or columns that will link the tables, as well as the type of join.

You can perform an inner, left outer, right outer, full outer, or cross join.

Note: You can create a recursive join by selecting the same data source twice.

When you link data sources, DataMigrator identifies each table with Tn, where n is the order in which the table was chosen. For example, the first source chosen would be identified as T1, the second as T2, and so on.

DataMigrator creates join syntax in ANSI 92 SQL. For example, when selecting the tables Product and Vendor, and columns VENDORNUM and VENDOR_NUM, DataMigrator generates the SQL syntax:

SELECT...FROM
Product t1 INNER JOIN VENDORS T2 on t1.vendornum=T2.vendor_num

Top of page

x
Reference: Considerations for Join Performance

iWay lets you create joins across similar data sources, different data sources, and across servers (also known as cross-platform joins).

In general, cross-platform joins and joins without key fields can create many intermediate files, depending on the number of tables joined. This issue may be less significant for some iWay platforms. For example, the Server for z/OS can use hyperspaces and alternate sort utilities. If performance is a concern, you should carefully evaluate the impact of your joins.

To improve performance, try any of the following:


Top of page

x
Procedure: How to Automatically Add Join Objects

The default settings of the DMC automatically create join objects. If join objects are not being created, you can change your settings so that join objects are created automatically:

  1. On the Home tab, in the Tools group, click Options.
  2. Select Data Flow Designer from the General tree.
  3. Select the Add Join Object if needed check box in the On Add Source section.
  4. Click OK.

Top of page

x
Procedure: How to Join Data Sources

Join objects are created automatically when you drag additional data sources into the data flow workspace if the DMC is using the default settings.

You can also add a join by right-clicking in the data flow workspace, and selecting Add Join. In that case, you must connect the data source to it by right-clicking it and dragging it to the join object.

  1. To complete the join, double-click the join object, or by right-clicking a join object and selecting Join Editor.

    The Join Editor window opens, listing the columns from the two data sources in the Left and Right Source Columns lists. For more information, see Join Editor Window.

    If the two data sources have a column with the same name and format and that column is a key column in one of the sources, an inner join will automatically be created for those columns. This default join can be changed, if necessary.

  2. If you want to join columns with different names, select one column from each list and click the equal sign to create the join.

    The equality condition will appear in the Expression box under Join Conditions.

    DataMigrator supports both equi and non-equi (conditional) joins. In an equi-join, the condition is one of equality, while a non-equi join uses any other condition, such as greater than, less than, and so on.

    Note: You can delete the condition from the Expression box by selecting it and clicking the Delete Condition button.

    You can edit the condition by selecting it in the Expression box and clicking the Edit condition button.

  3. Select the type of join you want from the Join Type drop-down menu, by clicking the join diagram, or right-clicking anywhere in the diagram and selecting a join type from the menu.
    • Click the area where the circles intersect for an inner join. This is the default.
    • Click the left circle for a left outer join.
    • Click outside the circles for a cross join or Cartesian product.
    • Click the right circle for a right outer join.
    • Click an inner circle a second time for a full outer join.

    The type of join you create will be graphically displayed in the workspace.

  4. To add a calculation or SQL function to the condition, select it and click the Edit condition button. When the Join Calculator opens, enter your calculation in the Conditions box by typing it or using the Columns/Variables and Functions tabs, and the calculator buttons. For details, see Join Calculator. For information about using functions, see Using an SQL Function. For information about using variables, see Using Variables in a Flow.
  5. To define a new condition, click the Insert condition button, which will launch the Join Calculator where you can create the condition.
  6. To test the join, click the Test Join button.
  7. Click OK to save the join.
  8. Drag another synonym into the workspace to the left of the SQL object.
  9. Double-click the second join object.
  10. Select one column from each list and click the equal sign to create the join.

    The equality condition will appear in the Expression box under Join Conditions.

  11. Click OK.

A data flow with three joined sources looks like this:

3 join example


Top of page

x
Procedure: How to Create a Join Condition With the Join Calculator
  1. From the Join Editor window, click the Add new row button, and then click the Calculator button.

    The Join Calculator opens.

  2. Enter your calculation in the Expressions box by typing it or using the Columns/Variables and Functions tabs, and the calculator buttons. For information about using functions, see Using an SQL Function. For information on using variables, see Using Variables in a Flow.
  3. Click OK.

Top of page

x
Procedure: How to Edit a Join Condition With the Join Calculator
  1. From the Join Editor window, select the condition and click the Edit condition button.

    The Join Calculator opens.

  2. Edit the calculation in the Expressions box by typing or using the Columns/Variables and Functions tabs, and the calculator buttons.
  3. Click OK.

Top of page

x
Reference: Join Editor Window

The Join Editor window is available from the workspace by double-clicking a join object or by right-clicking a join object and selecting Join Editor.

Join Editor

The Join Editor window contains the following fields and options:

Left Source

Displays the available columns from the first data source.

Next to each column name there is a symbol that indicates regular columns, key columns, index columns, or virtual columns.

Note: Clicking a column heading will sort the column grid on that heading.

Right Source

Displays the available columns from the second data source.

The columns symbols are:

Symbol

Description

Key column

Regular column

Index column

Virtual column

=

Establishes the relationship between the selected columns as equal.

Find button Find

Allows you to search for a column.

Insert Condition button Insert condition

Opens the Join Calculator to create a condition.

Delete Condition button Delete Condition

Deletes the selected condition.

Edit condition button Edit condition

Opens the Join Calculator to edit a condition.

Join Analysis

Opens the Join Analysis window where the join condition is analyzed to show the number of rows and distinct rows that would result from a left outer, inner, and right outer join.

Test join button Test join

Tests the join conditions.

Note: If you have not selected columns, test join displays the keys columns from the tables in the join. If you have selected columns, it displays the columns selected. If neither table has keys and no columns were selected, test join displays an empty answer set.

Join Conditions

Lists the conditions by which the two data sources are connected.

Join Type

Enables you to select the join type from the drop-down menu or by clicking in the join diagram, which graphically represents the type of join being used.



x
Reference: Join Calculator

The Join Calculator is available from the Join Editor window by selecting a row and clicking the Calculator button.

The Join Calculator contains the following fields and options:

Conditions window

Displays the expression.

Columns/Variables tab

Displays available columns and variables.

Functions tab

Displays a list of SQL functions that are available for your transformations.

Function Assist button

Allows you to specify parameters for the function through a dialog box when creating or editing a transformation.

Calculator buttons

Insert numbers and operators.

The following operators are available in both the Join and Filter calculators:

BETWEEN

Specifies data will be returned if the value of the field falls between the values entered on either side of the word AND. For example:

RETAIL BETWEEN 20 AND 30
IN

For alphanumeric data, specifies data will be returned if the field value is a substring of the right operand.

IS NULL

Specifies that records with a null value for the specified column are returned.

LIKE

Returns data if the value of the field matches the character string on the right operand. In the character string, use the underscore character (_) to represent any single character, and the percent character (%) to represent any sequence of characters. For example, the following string returns the value TRUE if LAST_NAME contains the string SMITH anywhere inside it (for example, SMITH, SMITHSON, SILVERSMITH):

LAST_NAME LIKE '%SMITH%'

The following string returns the value TRUE if LAST_NAME is exactly five characters long and the first letter is S.

LAST_NAME LIKE 'S_ _ _ _'
<

Returns data if the value of the field is less than the right operand. For example, the following string restricts the query to those employees who earn less than $30,000:

SALARY < 30000
<=

Returns if the value of the field is less than or equal to the right operand.

=

Specifies data if the field value equals the right operand.

AND

Is used in combination with the BETWEEN operator.

NOT

Is used in combination with other relational operators to negate them. For example, the following string returns all records where the value of LAST_NAME is not null:

LAST_NAME IS NOT NULL
>

Specifies data will be returned if the value of the field is greater than the right operand.

>=

Specifies data will be returned if the value of the field is greater than or equal to the right operand.

<>

Specifies data will be returned if the left operand is not equal to the right operand.

OR

Inserts the operator OR.

( )

Adds parentheses.

''

Inserts two single quotation marks. Enter alphanumeric test values between these.

a->A

Converts selected text to uppercase.

A->a

Converts selected text to lowercase.

Date

Opens the Date Editor window which lets you use the current date, or specify a date from the a calendar.

Datetime

Opens the Date Editor window which lets you use the current date and time, or specify a date and time from the calendar.


Top of page

x
Reference: Join Analysis Window

The Join Analysis window is available from the Join Editor window by clicking on the Join Analysis button.

Join Analysis

The Middle column shows the number of rows (Count) and unique rows (Distinct Count) that the entered join criteria would return for an Inner join.

The Left and Right columns show the number of additional values and rows that would be returned for their respective join type.

From the example shown in the above image, a Left outer join would return 60 additional rows with 7 unique values, in addition to the rows returned by the inner join.

Note that all of the numbers in each row/column are also hotlinks. For example, if you double-click 7, a Test Results window opens, as shown in the image below. This window shows the 7 values for EMPID that were in the left source, but not found in the right source.

Test Results


Top of page

x
Reference: Join Object Options

After a join has been added to the workspace, the following options are available in the shortcut menu:

Toggle

Opens a window that allows you to view the join expression and sample data.

Refresh

Join Editor

Opens the Join Editor which allows you to create or edit the join conditions.

Add

Opens a submenu with the following options. Note that these options are only available when the Join object does not already have two inputs.

  • Source. Opens the Select Synonym panel to add a new synonym to the data flow connected to this join object. When adding the second synonym the Select Synonym opens with Show synonyms with matching keys filter already enabled.
  • Join. Adds a new Join object as input to this Join object.
  • Select. Adds a new Select object as input to this object.
  • Union. Adds a new Union object as input to this object.
Delete

Deletes the selected Join object.

Properties

Opens the properties panel for the selected Join object.

Error Info

This option is only available if there is an error. Displays a dialog box with an error message. For more information, click Details.


Top of page

x
Reference: Test Individual Join Window

The Test Individual Join window is available from the Join Editor window by selecting a join and clicking the Test Join button. It shows sample data from the join.

Test Individual Join Window


iWay Software