VI Join Process Object

The Visual Integrator (VI) Join process object performs SQL-style joins on inputs to the data flow.

VI Join Process Object Icon

The Join object discards records from one or both flows if they do not join any records from the other flow and duplicates records when both flows contain multiple records. The Join object expands the ability of the Lookup process object in that it implements standard SQL join types (inner, outer, left outer, right outer, and merge) and performs Cartesian products for duplicate records.

Unlike the Lookup object, the Join does not require the lookup flow to completely fit into memory, but the join might be slower if the lookup flow is not read into memory. It does require that both flows are sorted according to the join columns. If the flows are not sorted properly, a warning message displays, and the results of the join might be incorrect. Use the autosort attribute to automatically sort one or both of the Join inputs.

The Join process object has Closedthree panes where you set attributes.

VI Join Object All Panes

You set attributes for the Join process object in the Closedobject attributes pane.

Attributes for the VI Join object

Attribute Description
Input

Defines the primary input for the Join object. This primary input and the Secondary_Input are required attributes.

  1. Place an input object on the task flow, and set its input attributes.

  2. Place a Join object on the task flow.

  3. With the Join object selected, click its Input attribute's browse button, and move the pointer to the task flow.

    Note that a Closeddotted-line connector is attached to the Join object.

    Selecting the primary input for a Join object

  4. Attach this connector to the primary input object.
Secondary_Input

Defines the secondary input for the Join object. This secondary input and the Input are required attributes.

  1. Place a second input object on the task flow, and set its input attributes.

  2. With the Join object selected, click its Secondary_Input attribute's browse button, and move the pointer to the task flow.

    Note that a Closeddotted-line connector is attached to the Join object.

    Attaching the secondary input

  3. Attach this connector to the secondary input object.
Join_Type

Defines the join type to perform. These types closely match classical relational database logic. Select one of the following types:

  • inner (default)—Only rows that have matching join columns in both input flows are returned in the output flow. If a flow has duplicate rows with the same join column values, then a Cartesian product of the matching rows are returned in the output flow. If a column exists on both flows, the primary input flow (on the left of the column grid) values are kept.
  • outer—All rows are returned in the output flow. If a row in one flow does not have a match in the other flow, then it is matched with a row of blank values. As with the inner join, a Cartesian product is returned for duplicate rows when present in both flows.
  • left outer—All rows from the first (left) flow are returned. If the join column values for a row in the left flow does not match the join column values for any row in the right flow, it is matched with a row of blank values for right flow columns. If a flow has duplicate rows with the same join column values, then a Cartesian product of the matching rows are returned in the output flow.
  • right outer—All rows from the second (right) flow are returned. If the join column values for a row in the right flow does not match the join column values for any row in the left flow, it is matched with a row of blank values for left flow columns. If a flow has duplicate rows with the same join column values, then a Cartesian product of the matching rows are returned in the output flow.
  • merge—The two input flows are merged according to the sorted join columns. This is the same as using the Concat object on the two input flows, except that the rows from the input flow are interleaved based on the sort instead of one input flow appearing after the other. This join is useful in situations when you want to maintain the sort order of the rows. All rows from the input flows appear in the output flow.
Auto_Sort

Turns on the automatic sort. This sorts an input flow by creating internal Sort object functionality. This sort uses default values for Sort attributes, such as Temp_Directory and Sort_Size. Use an explicit Sort object if you need to set these values. For more information, see VI Sort Process Object.

Select one of the following options:

  • true—Sorts both flows according to their respective join columns.
  • false (default)—Performs no sort.
  • left—Sorts the left (primary) flow according to its join columns.
  • right—Sorts the right (secondary) flow according to its join columns.
Case_Sensitive

Controls whether comparisons are case-sensitive.

  • true—Comparisons are case-sensitive.
  • false (default)—Comparisons are case-insensitive.
Locale

Controls whether or not comparisons for join columns are performed in a locale-specific manner.

  • true—Comparisons are performed in a locale-specific manner. Set to true, this attribute overrides the Case_Sensitive attribute.
  • false (default)—Comparisons are not performed in a locale-specific manner.

This attribute is similar to the Locale attribute in the Sort process object. For more information, see VI Sort Process Object.

Each object has an area where you can enter comments.

It is a DI best practice to enter a note for every object in a VI script. You can set a VI preference to give a warning for each object without a note. For more information, see Visual Integrator Preferences.

ClosedJoin process object with comments

Comments pane of the VI Join object

The ClosedJoin column grid displays the primary input on the left, the secondary input on the right, and the join columns that you specify in the middle.

Join grid showing example of column join

The Join column grid has three parts:

  • Primary Input Grid (left)—Lists the columns defined in the primary input object where you can select the columns to join. The Source Object column displays the object name where the columns entered the flow. Double-click the Source Object to change the task flow focus to that source object.
  • Join Grid (middle)—Displays the columns that you have designated for the join. See the following section about joining columns.
  • Secondary Input Grid (right)—Lists the columns defined in the secondary input object where you can select the columns to join with. The Source Object column displays the object name where the columns entered the flow. Double-click the Source Object to change the task flow focus to that source object.

To set the columns for the join:

  1. From the Primary Input grid on the left, select the column, and click the arrow between the left and middle grids.

    The column is added to the ClosedLeft Column column of the middle grid.

    Adding the left column to the join grid

  2. From the Secondary Input grid on the right, select the column you want to join, and click the arrow between the right and middle grids.

    The column is added to the ClosedJoin With column in the middle grid.

    Adding a "Join with" column to a join

  3. Repeat steps 1 and 2 for any additional joins.

  4. Edit the Join Order column if necessary.

    NOTE: To treat the columns as numeric values, select the check box under the Numeric column.

Given the following two input flows:

ClosedInvoice input

Example of invoice input for a VI Join process object

ClosedCustomer input

Example of customer input for a VI Join process object

Create the following ClosedJoin object settings where a merge join is performed with Product from the Invoice input and Customer ID from the Customer input.

Example of settings for a VI Join process object

You get the following merged Closedoutput data:

Example of output from a VI Join process object