Full Outer Join in Join Stage

[tech_savvy]

Hello,
I have problem in using the FULL OUTER JOIN in JOIN Stage. There are two inputs and i am using a Join Stage and I need all the records of the Inputs so I am using FULL OUTER JOIN Option. If we use Full OUTER JOIN for the key attribute there will be extra columns for that key(i am Using FOUR Keys) as LEFT.Attribute and RIGHT. Attribute but at the output I need it as SINGLE attribute. Please Help me out

[jhmckeever]

A Full Outer join delivers rows from the LEFT dataset which don't match the RIGHT, and vice-versa. This means you NEED to be able to differentiate which input links each row originated from.

A common approach is to use a downstream transformer to check which key columns contain nulls and execute some logic or forward rows to output links appropriately.

To consolidate your key values into single values, try using column derivations something like the following ...

Code: Select all

OutKey1 = NullToValue(LEFT.Key1, RIGHT.Key1)
OutKey1 = NullToValue(LEFT.Key2, RIGHT.Key2)
etc.

HTH,
John.

[tech_savvy]

I have tried with the option that is given in the Transformer Stage but the data is not coming through the Transformer.

[ray.wurlod]

You need to bring all columns into the Transformer stage then come up with some kind of merge strategy. For example

Code: Select all

If IsNull(LEFTcol) Then RIGHTcol Else LEFTcol

Be careful to get this the right way around to handle the situation where neither of the columns is null - that is, that an inner join would have returned a row.