Join

Operation Name

Join

Function Overview

Joins the two input data.

Data Model

Table model type.

Refer to "Table model type schema" for input/output schema.

Properties

Basic settings

Item name	Required/Optional	Use of variables	Description
Name	Required	Not available	Enter the name to show on the script canvas.
Input data (LEFT)	Required	Not available	Select a component on the script canvas.
Input data (RIGHT)	Required	Not available	Select a component on the script canvas.

Required settings

Item name	Required/Optional	Use of variables	Description	Remarks
Operation type	Required	Not available	Select a type of operation. [INNER JOIN]: (Default) [LEFT OUTER JOIN]: [RIGHT OUTER JOIN]:	For details, refer to "Operation Type".
Join key configuration	Required	-	Specify columns which would be keys for join operation. Each column can be operated with the following buttons. [Add]: Adds a key. [Delete]: Deletes a key.	When multiple columns are specified as keys, the values of the key columns will be compared and the data that is matched with all columns will be joined.
Join key configuration/Input data (LEFT)	Required	Not available	Select the column name of [Input Data(LEFT)] that will be a key.	Displays the index that shows the order after the column name. When the value of [Input data (LEFT)] is selected or changed, column name will be updated.
Join key configuration/Input data (RIGHT)	Required	Not available	Select the column name of [Input data (RIGHT)] that will be a key.	Displays the index that shows the order after the column name. When the value of [Input data (RIGHT)] is selected or changed, column name will be updated.

Output settings

Item name	Required/Optional	Use of variables	Description	Remarks
Configure output manually	Optional	Not available	Select whether or not to set columns to be output manually. [Checked]: Output target is set manually. [Not Checked]:(Default) Output target will be automatically set according to the content of the specified property items.	When it is not checked, the output target would be in the following order. Setting order of [Join key configuration] Order of the input schema of [Input data (LEFT)] (The columns specified by [Join key configuration] are excluded) Order of the input schema of [Input data (RIGHT)] (The columns specified by [Join key configuration] are excluded)
Output target configuration	Required	-	Specify columns to be output. Each column can be operated with the following buttons. [Up]: Moves the output order of the output target upwards by one . [Down]: Moves the output order of the output target downwards by one. [Add]: Adds an output target. [Delete]: Deletes the output target.	Enabled when [Configure output manually] is checked.
Output target configuration/Data source	Required	Not available	Select a data source of the output target. [Join key (LEFT/RIGHT)]: [Input data (LEFT)]: [Input data (RIGHT)]:
Output target configuration/Column name	Required	Not available	Select the name of a column to be output. Column name corresponding to the value specified in [Output target/Data source] will be displayed as choice. [<Column name of Input data (LEFT)>/<Column name of Input data (RIGHT)>]: Displayed when [Join key (LEFT/RIGHT)] is selected. [<Column Name of Input Data(LEFT)>]: Displayed when [Input data (LEFT)] is selected. [<Column Name of Input Data(RIGHT)>]: Displayed when [Input data (RIGHT)] is selected.	When the value of [Output target configuration/Data source] is selected or changed, column name will be updated. Displays the index that shows the order after the Column name. When you want to select a column specified in [Join key configuration], select [Join key (LEFT/RIGHT)] in [Output target/Data source].

Comment

Item name	Required/Optional	Use of variables	Description	Remarks
Comment	Optional	Not available	You can write a short description of this adapter. The description will be reflected in the specifications.

Schema

Input Schema

The same as the schema of the input source component.

Output Schema

Depending on the [Output Settings], the number of columns will vary.

For schema structure, refer to "Table model type schema".

Loading Schema in Mapper

Output schema will be loaded automatically while input schema should be done manually.
Specify the schema of the data to be read.

For more details, refer to "Edit Schema".

Mass Data Processing

Mass Data Processing is not supported.

PSP Usage

PSP is not supported.

Available Component Variables

Component variable name	Description	Remarks
message_category	Stores the category to which corresponding message code belongs to, when error occurs.	Default value is null.
message_code	Stores its corresponding message code of occurred error.	Default value is null.
message_level	Stores the severity of the corresponding message code of the occurred error.	Default value is null.
error_type	Stores the error type when error occurs.	Default value is null. The error type will be in the below format. Example: "java.io.FileNotFoundException" The stored details may vary depending on the DataSpider Servista version.
error_message	Stores the error message when error occurs.	Default value is null. The stored details may vary depending on the DataSpider Servista version.
error_trace	Stores trace information when error occurs.	Default value is null. The stored details may vary depending on the DataSpider Servista version or the client application to be executed.

Schema Modification of Input Source Component

When the schema of the component specified in [Input data (LEFT)] or [Input data (RIGHT)] is modified, to reflect those changes to join operation, open the property setting dialog of join operation and click [Finish] button.

When the schema structure of the component specified in [Input data (LEFT)] or [Input data (RIGHT)] is modified (swapping or deletion of schema elements), [Join key configuration/Input data (LEFT)], [Join key configuration/Input data (RIGHT)], and [Output target configuration/Column name] must be modified accordingly.
The reason behind is that the join operation keeps the information of columns by their orders (indices) rather than by their names.

Null and Empty String

When there is a null value existing in the value of the column which is specified in [Join key configuration], the value will be processed as join key value.

When there is an empty string in the value of the column which is specified in the [Join key configuration], the behavior of the operation varies depending on the data type of another specified column.
- An empty string will be treated as join key value when the columns are both of String type.
- An error occurs when one of the column is of String type and the other is not.

Operation Type

Select one of the following methods of join with the operation type.

Description of items

Item name	Description
INNER JOIN (default)	Retrieves only the records for which the table key values of both [Input data(LEFT)] and [Input data (RIGHT)] are matched against each other.
LEFT OUTER JOIN	Retrieves all records from the table of [Input data (LEFT)], and from the table of [Input data (RIGHT)] the records whose key values are matched against the key values within the table of [Input data (LEFT)].
RIGHT OUTER JOIN	Retrieves all records from the table of [Input data (RIGHT)], and from the table of [Input data (LEFT)] the records whose key values are matched against the key values within the table of [Input data (RIGHT)].

Example: Join the below two tables "Products" and "Sales" using "Product number" as key.

Products

Sales

Product number	Product name
1	Product A
2	Product B
4	Product D

Product number	Order quantity
1	10
2	30
3	50

In case of [INNER JOIN (default)]

Product number Product name Order quantity

1 Product A 10

2 Product B 30

In case of [LEFT OUTER JOIN]

Product number Product name Order quantity

1 Product A 10

2 Product B 30

4 Product D

In case of [RIGHT OUTER JOIN]

Product number Product name Order quantity

1 Product A 10

2 Product B 30

3 50

Product number	Product name	Order quantity
1	Product A	10
2	Product B	30

Product number	Product name	Order quantity
1	Product A	10
2	Product B	30
4	Product D

Product number	Product name	Order quantity
1	Product A	10
2	Product B	30
3		50

Joining Columns for Different Data Types

If the data types of the columns specified in the join key are different, perform the following type conversion.

Conversion of data type

Data type of the column of Input data (LEFT)	Data type of the column of Input data (RIGHT)
Data type of the column of Input data (LEFT)	byte	short	int	long	float	double	BigDecimal	boolean	String	Date	byte[]
byte	―	short	int	long	BigDecimal	BigDecimal	BigDecimal	×	byte	×	×
short	short	―	int	long	BigDecimal	BigDecimal	BigDecimal	×	short	×	×
int	int	int	―	long	BigDecimal	BigDecimal	BigDecimal	×	int	×	×
long	long	long	long	―	BigDecimal	BigDecimal	BigDecimal	×	long	×	×
float	BigDecimal	BigDecimal	BigDecimal	BigDecimal	―	BigDecimal	BigDecimal	×	BigDecimal	×	×
double	BigDecimal	BigDecimal	BigDecimal	BigDecimal	BigDecimal	―	BigDecimal	×	BigDecimal	×	×
BigDecimal	BigDecimal	BigDecimal	BigDecimal	BigDecimal	BigDecimal	BigDecimal	―	×	BigDecimal	×	×
boolean	×	×	×	×	×	×	×	―	boolean	×	×
String	byte	short	int	long	BigDecimal	BigDecimal	BigDecimal	boolean	―	×	×
Date	×	×	×	×	×	×	×	×	×	×	×
byte[]	×	×	×	×	×	×	×	×	×	×	×

How to read the table

Symbol or text	Description
<Internal data type>	Performs type conversions to <Internal data type>.
―	No conversion will be performed for the same data type.
×	Joining columns of specified data types is not supported.

The internal data type char is treated as String.

When converting String to boolean, "true" will be converted to true and other than that will be converted to false.

Specification Limits

Multi-thread processing is supported.
For the specification limits on multi-thread processing, refer to "Specification Limits".

When specifying [Join key configuration] and [Output target configuration], the output schema of input source component must be configured.
For components requiring manual schema settings, load schemas with Mapper.

Order of the result data of the join operation is not assured.

Even when mass data processing is performed by input source component, data is temporarily held in the memory during the execution of join operation.
Therefore, when the data amount is massive, an OutOfMemoryError might occur even with mass data processing enabled.
In such case, expand the memory allocation of DataSpiderServer.

Main Exceptions

Exception name	Cause	Solution
InputDataNotFoundException	[Input data] is not specified.	Specify the [Input data], or draw an input data flow.
InvalidInputTypeException	[Input data] is not table model type.	Check whether the component specified in [Input data] is table model type.
InvalidPropertyConfigurationException Value is not specified in <property name>.	Value is not specified in [<property name>].	Specify the [<property name>].
InvalidPropertyConfigurationException Value is not specified in <property name2>of the row [<row number>] in [<property name1>].	Value is not specified in [<property name2>].	Specify the [<property name2>].
InvalidPropertyConfigurationException The value specified in the row [<row number>] of <property name>is duplicated.	The value specified in [<property name>] is duplicated.	Check whether the value specified in [<property name>] is appropriate.
InvalidPropertyConfigurationException The value specified in <property name2> of the row [<row number>] of [<property name1>] is duplicated.	The value specified in [<property name2>] is duplicated.	Check whether the value specified in [<property name2>] is appropriate.
ConversionFailedException Column value of <input data> cannot be processed as specified join key.	The contents of [<input data>] contains some values that cannot be processed as per the specification in [Join key configuration].	Check whether the [<input data>] is compatible with the contents specified in [Join key configuration], or whether any empty string is contained.