Setting Up Join Sources
You can join one source with another source (intermediate target) and map the results to a target. The sources can have different data types. For example, you can join an SQL database table with an ASCII file. In a database join, data is not physically joined. You cannot join data sources with multiple record types. The join target will be able to connect to any single-record type connector.
While mapping, all the fields from both the left and right sides of the join are available for mapping. This makes it easy to determine unmatched rows (checking to see if the key from the left or right side is empty), and removes any requirement for name resolution. All fields mapped on the target side of the join (presumably mapped from the left source) become the key fields.
For events, you need to only provide a OutputRecord action in the RecordStarted event, on a control link from the left source to the join intermediate target. The join itself handles writing all the appropriate matches each time it receives the action. In addition, by knowing the left source, it handles all end-of-source processing internally (writing unmatched records when doing a right or full outer join).
All events regarding the join output is from the source side of the join intermediate target, since this controls all record output as a result of the join. The left source can trigger actions on other control links, but they are not related to the join.
In addition, the join is never the target of an OutputRecordset action.
To add a join:
1. On the Sources tab, click the down arrow next to 
icon and select Join.
icon and select Join.A join is added with the default name JOIN_1.
2. In the Join Properties section, specify the following:
Property Name | Description | |
Name | Displays the default name of the join. To change the name, click within the text box and type the required name. The updated name is displayed in the Select Source drop down list and Map Navigator. | |
Description | Type a description for the join. | |
Properties | ||
Schema Mismatch | Select the required value: • Treat as Error (default value) • Use Map Schema • Use Connection, Match by Position • Use Connection, Match by Name | |
Join Type | Select the required value: • FullOuter: Returns all records from both sides of join. • Inner: Returns records identical on both sides of join. Discard unmatched records. • LeftOuter: Returns all records in source or join on left side, even if no records match on right side. • RightOuter: Returns all records in source or join on right side, even if no records match on left side. The connection for the join target is considered the right side of the join. | |
Join Left | If you have three sources or additional intermediate targets, you need to identify the source for the created join. That is, the left-side of this particular join. By default, it is SOURCE_1. | |
Distinct Rows | During join, if you want distinct rows, then set this option to true. Normally, a join can produce more records than exist in either table, if either table has more than one record matching a given key. If this property is set true, then only one row from each table with the given key produces the output. For example, three records from left and two records from right, then you can have only one record in the target. Default value is False. | |
3. In the Join Connection section, specify the source connection details:
a. Do one of the following:
– From the Choose Connector drop-down list, select a source connector.
– In Or Connection, click Browse and select an existing source connection file.
The connection parts are displayed. Also, the selected connector’s properties are displayed on the right.
b. Specify the information.
For information about the selected connector and its properties, see Map Connectors and for information about the connection details, see Setting Up Source Data.
c. Click Connect to connect to the selected connector.
4. In Confirm Schema section, click Browse and specify the schema.
The contents of the schema file is displayed in the table on the right-side.
The join is added at the following locations on the Mapping tab:
• Simple View: Join is added to the Select Source drop-down menu.
• Advanced View:
– Join is added to the Source tree with the following sub nodes:
• All Record Types
• All Record Instances
• Source Events
– Join is added to the filter. If you select a join, anything defined before the selected join is displayed in the tree view.
Also, the join is listed in the EZscript Expression Editor > Fields section if the join schema is selected.
Specifying New Schema for Join
You can add a new schema for join and edit or delete an existing schema in the Mapping Advanced View. The changes made in this view will reflect on the Sources tab or any other location where the Join is displayed.
After adding a Join from the Sources tab, you can do the following to specify a new schema for the Join:
1. On the Mapping tab, go to Advanced View and select the newly added join from the filter. For example, JOIN_1.
2. In the source tree, expand the newly added join node.
3. Click All Record Types.
4. Click 
to add a record.
to add a record. Note: You can add a single record only. After adding a record, the icon is disabled.
5. If required, edit the Name and add a Description. By default, the name is R1.
6. Expand all the child nodes of All Record Instances and click <recordname>_group. For example, R1_group.
7. Click 
to add fields and specify the required information for each field.
to add fields and specify the required information for each field.8. Save the map.
Deleting Join
To delete a join, on the Sources tab, from the Select Source drop-down list, select the join that you want to delete and click 
icon. The join is deleted.
icon. The join is deleted.Last modified date: 08/02/2023