Issue with Match and non Matching from Join Stage

[Nagasudheerkumar]

Hi,

I have a requirement as below

Code: Select all

                            B(table)
                               |
A(table)---Join Stage(fullouterJoin)------------->   Transformer ---------->   Matched                                                                                             
                                                    |          | 
                                        Unmatched from A(Tfm)   Unmatched from B(Tfm)                              

I have 10 cols in A in which 5 cols should be merged to create a key(ID).
I have 6 cols in which 1 column is key(ID).

I am getting corrected output for unmatched from A,but not for Matched and unmatched from B.

constriant for Matched: left key and right key should not be null.
constriant for unmatched from A: Isnull of right key
constriant for unmatched from B: Isnull of left key Or Isnull of other cols and col <>0(some are number cols)


I have duplicates in matching keys(ID).

A count:
5000
B Count:
600000
Matched should be 4710 and i am getting 28800
unmatched from A 290 which is correct
unmatched from B should be 595290 but i am 597290

Can anyone suggest me what is going wrong in Matched and unmatched from B.

Thanks
Sudheer

[ray.wurlod]

1. Encase your "diagram" in Code tags and tidy it up so we can clearly understand what's happening.

2. Inform us how you are partitioning and sorting your data on the inputs to the Join stage.

3. Duplicates are OK with a join.

[Nagasudheerkumar]

I have resubmitted the post and I hope it is able to understand, now i did a test job for the matched output and i am able to get the correct count(4710). How I did it is I have taken the unique constriant combination of Table A after transformer(after Join) I kept Sort and filter it with 1(cluster key).

Now the hunt is for last link count which is very less count than expected for unmatched Table B.

Full outer Join is generating 9867 more records than expected( 605000) might be because of Duplicates in both the inputs.

I am using Auto partitioning