Join Database Data
In mappings that read data from databases, you can join database objects such as tables or views by adding a Join component to the mapping. For example, you could combine data from two or more tables bound by foreign key relationships, which is the typical way data is stored in relational databases. The result would be the same as if you ran against the database an SQL query where two or more tables are joined by means of an INNER JOIN (or LEFT JOIN, if applicable) operation.
Depending on the kind of data connected to the join component, the join operation can happen either in standard (non-SQL) mode, or in SQL mode. Joins in non-SQL mode are undertaken by MapForce, while joins in SQL mode are undertaken by the database from which the mapping reads data.
Joins in non-SQL mode are more flexible because they support more component types as input (for example, the join can be between tables from different databases, or between XML structures and database tables). For an example of a non-SQL join, see Example: Join XML Structures. On the other hand, a non-SQL join causes the mapping engine to perform memory-costly comparisons (because the total number of comparisons represents the cross-join, or Cartesian product, of all joined structures). Usually this process is very fast and causes negligible load in mappings which are not data-intensive; however, if the joined data sources consist of a huge number of records, then the mapping will require significant time to execute. If your mappings must process a very large number of records, consider licensing MapForce Server Advanced Edition, which includes dedicated join optimization to speed up the mapping execution.
A join in SQL mode accepts only eligible database objects as input (such as tables or views), so it is not as flexible as a non-SQL join. However, it offers better mapping performance because it is executed natively by the database. For further information, see Joins in SQL Mode.
| Note: | Using a Join component is not the only way to join database tables or views. Joins applicable to databases can also be performed by using SQL SELECT statements, see SQL SELECT Statements as Virtual Tables. A major difference between SQL SELECT statements and Join components is that the former are written by hand so they might provide more flexibility. Join components are a simpler alternative if you do not feel comfortable writing SQL statements by hand. |
To add a Join component:
1.Set the mapping transformation language to BUILT-IN (to do this, either click the 
toolbar button, or use the Output | Built-In Execution Engine menu command).
2.On the Insert menu, click Join. Alternatively, click the Join ( 
) toolbar button. The Join component appears on the mapping. By default, it accepts data from two structures, so it has two nodes/rows inputs. If necessary, you can add new inputs to the join by clicking the Add Input ( 
) button, see Joining Three or More Structures.
3.Connect the structures that are to be joined to the nodes/rows items of the join component.
4.Add the condition for the join (or multiple conditions). To do this, right-click the Join component and select Properties. Join conditions can also be added directly from the mapping, by connecting the Boolean result of some function to the condition item of the Join component. In certain cases when database tables are joined, the join condition (or conditions) can be created automatically by MapForce. For further information, see Adding Join Conditions.
Notes:
•Join components are supported when the target language of the mapping is set to BUILT-IN. Code generation in C#, C++, or Java is not supported.
•When a structure is not a valid or supported input source for the join, MapForce displays hints either immediately directly on the mapping, or in the Messages window, when you validate the mapping (see Validating Mappings).
•Join components should not be connected to input parameters or results of inline user-defined functions. If such connections exist, validation errors will occur during mapping validation.
•When you connect eligible database components (such as tables or views) directly to a Join component, an SQL mode ( 
) button automatically appears at the top-right corner of the Join component. When enabled, this button provides special SQL features applicable to the join operation (see Joins in SQL Mode).
•It is not possible to connect the output of the joined item to another Join component. If necessary, however, you can connect a partial result of one join to another one.