left outer join and where caluse

[times29]

Hi,
I have

Code: Select all

 table a                          table b
col1   col2  col             col1  b_col2   b_col3
105    500   507             105   500      507
105    500   506
105    101   201

Target result should be

Code: Select all

col1 col2 col3
105  500  506
105  101  201

i joined two table using join stage did left outer join from table a to table b on col1 then in transformer i put a constraint
col2<>b_col2 and col3<>b_col3 and the constraint is not working as it filter all three table a records

any idea what i am doing wrong?

Thanks

[ray.wurlod]

Do you get three rows into the Transformer stage?

What would you expect the result to be?

The correct test for a failed lookup would be IsNull(col2).

[times29]

Yes transformer did get three rows in which is good but after transformer
constraint rows_out are zero

i want to see two below rows out of transformer

105 500 506
105 101 201

[ArndW]

So your incoming data to the transform stage after the join is

Code: Select all

col1   col2  col3  b_col2  b_col3 
105    500   507  500      507 
105    500   506  500      507 
105    101   201  500      507 

Your logic in the constraint would seem to be correct. Try to remove the right side of the "AND" and see if you get the expected 1 output row; then repeat for the left side to see if you get the expected 2 output rows.

[PhilHibbs]

Target result should be

Code: Select all

col1 col2 col3
105  500  506
105  101  201

Why should this be the output? What's the rule that rejects the second row? At a guess, I'd be tempted to say "where col2<>b_col2 or (col2=b_col2 and col3=b_col3)", is that it? In english, "if Col2 matches then Col3 has to match as well".

[ankursaxena.2003]

Are you using Join Stage to join both the tables.

If you are using Join Stage then can you check link ordering in Join Stage.
I think link from Table b is doing left join with Table a.

[stuartjvnorton]

I think you need to explain what you actually want. Just giving a trivial example means that we can come up with too many different ways to interpret it, and therefore too many solutions.

Do you want everything from table 1 that isn't in table 2?