About Merge Join Component

Hi, I'm using a Merge Join Component of Inner Join type to retrieve from the right pipeline some records to append to the ones coming from left pipeline according to the join citerias defined on the compnent.

Is there any way to know which are the records coming from the left pipeline that doesn't match the join criterias?

In the following I'll try to do an axample.

LF pipeline:

Column0 Column1 Column2

1 aaaa aa11

2 bbbb bb11

3 cccc cc11

4 dddd dd11

RT pipeline:

ColumnA ColumnB

1 aa22

4 dd22

On exiting from the MergeJoin, defining “Column0” for LT as join key and “ColumnA” for RT and as output data all the columns of the LT pipeline and the only ColumnB from the RT pipeline it should be obtained the following records:

Column0 Column1 Column2 ColumnB

1 aaaa aa11 aa22

4 dddd dd11 dd22

and the records from the LT pipeline:

2 bbbb bb11

3 cccc cc11

shouldn't go in the output from the Merge Join Component.

What I need to know is which are these last lines because I need to manage them.

Thanks!

If you need to get both matches and mismatches; shouldn't you be using an left or full outer join instead? then you could place a conditional split to redirect and manage rows that did not match.

BTW, Have you loked at the Looukup transformation instead?

You could have a lookup based on the 2 set of row (assuming is an OLE DB data source) and then use the error output to redirect the rows that don't match. That way you get 2 outputs; the matches and the mismatches.

|||

Hi,

I read your answer and I think I need more details about it.

When you say:

“….shouldn't you be using an left or full outer join instead?”

I tell you:

If I use a left outer join, on output I have both matches and mismatches records. Can you tell me on what I have to do the conditional split? Can you show me any example to let me understand better?

When you say:

“…BTW, Have you loked at the Looukup transformation instead?”

I tell you:

In the package both the two sets of rows (LT pipeline and RT pipeline) doesn't came from a datasource but they are a result of an intermediate elaboration of data read at the beginning of package.

Thanks again!

|||When you use the LEFT OUTER JOIN, take columnB and test if it is NULL or not. If it is NULL then you are looking at a record that didn't have a match.|||

Thanks,

I'll try to do that!

|||Actually, in thinking about it, the better thing to do is to take COLUMNA and pass it through the merge join transformation. Check that for NULL, not COLUMNB.|||

SabAlo wrote:

Hi,

I read your answer and I think I need more details about it.

When you say:

“….shouldn't you be using an left or full outer join instead?”

I tell you:

If I use a left outer join, on output I have both matches and mismatches records. Can you tell me on what I have to do the conditional split? Can you show me any example to let me understand better?

just add a conditional split after the merge join to split the rows. You can evaluate if the the columnX is null or not; if it is null means there was not a match.

SabAlo wrote:

When you say:

“…BTW, Have you loked at the Looukup transformation instead?”

I tell you:

In the package both the two sets of rows (LT pipeline and RT pipeline) doesn't came from a datasource but they are a result of an intermediate elaboration of data read at the beginning of package.

Thanks again!

Fair enough.

|||

Hi,

I followed the suggestion to consider the columnA in the output results and it is working! I test the its value and it is really null if it doesn't match the merge criteria.

As regard the testing of the other column (B), my problem is that could contain null value and should be imppossible to distinguish one record from the other.

Anyway, thanks for the hint!!