SLIK provides a mechanism to read and
join on multiple source tables to a single destination table.
This feature is useful when you have a master/detail relationship and you
want to transfer all related rows as a logical unit.
This can either be performed as an aggregation or as a projection of
columns.
The tables are actually joined as part
of the Table Mapping process. The
Table Join form is simply used to define the type of join to be used.
To create a Table Join follow the
steps below:
1.
From within the Table Map Properties
form click on the Table
Join… button to open the Table
Join Properties form.
2.
The TableJoinID
field will be automatically populated with a system generated unique identifier.
The TableID
column will have populated with all multi-tables names defined in SLIK.
Each table is separated by a ‘,’ within it’s respective field.
For each TableID
specify each individual table’s column as the left and right column ID fields.
3.
Usually the Join Type is INNER JOIN
. LEFT OUTER
JOIN
and RIGHT OUTER
JOIN
options are also
available. Amend the JoinTypeID to
reflect the type of join required.
Note,
to support the OUTER JOIN types you must check the EnableSQL92 in the TableName
maintenance form.
Associated
SQL Statements
When the SLIK server processes a
multi-table join the SQL statement constructed can be viewed as;
SELECT
* or ColumnMap
List…
FROM
Src-Multi-TableNames
JOINTYPE
LeftColumn TO RightColumn…
WHERE
SrcWhereCondition
To join on more than one column simply
add another record with the same TableID.
At runtime the SLIK server queries the
database to determine the appropriate SQL syntax to use for a table join.
This can be either be an ANSI SQL92 join clause such as INNER JOIN
Left-Table.Column ON Right-Table.Column or the more common WHERE
Left-Table.Column
= Right-Table.Column.