The Join stage doesn’t offer reject handling for unmatched records (such as in an Inner Join scenario). So how can we capture unmatched records from join stage?
Let see!
If unmatched rows should be captured or logged, an OUTER join operation should be performed.
In an OUTER join scenario, all rows on an outer link (e.g., Left Outer, Right Outer, or both links within the case of Full Outer) are output regardless of the match on key values.
During an Outer Join, once a match doesn’t occur, the Join stage inserts NULL values into the unmatched non-key columns using the following strategy.
If the non-key column defined as nullable (On join input links), the DataStage inserts NULL values in the unmatched columns.
If the non-key column defined as not-nullable , the DataStage inserts default values based on the unmatched columns datatype.
For example, the default value for an Integer is Zero, the default value for a Varchar is an empty string(“”), the default value for Char is is a string padchar characters equal to the length of Char column.
Remember!
Because of this, care should be taken to alter the column properties to permit NULL values before the Join. This approach can be easily done by inserting a Copy stage and mapping a column from NON-NULLABLE to NULLABLE.
A Filter stage or Transformer stage can be used to test for NULL values in unmatched columns.
And we don’t stop there!
In some cases or as a best practice, it is better to use a Column Generator to add an ‘Indicator’ column, with a constant value, to each of the outer links and check that column for the constant once you have performed the join.
To make a point, using Column Generator stage is the best approach because it isolates your match/no-match logic from any changes in metadata. This logic is also handy with Lookups that have multiple reference links.
