Page 1 of 1
left outer join and where caluse
Posted: Fri Oct 05, 2012 8:45 pm
by 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
Posted: Sat Oct 06, 2012 2:44 am
by 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).
Posted: Sat Oct 06, 2012 4:53 am
by 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
Posted: Sat Oct 06, 2012 5:40 am
by 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.
Re: left outer join and where caluse
Posted: Mon Oct 08, 2012 10:01 am
by PhilHibbs
times29 wrote: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".
Posted: Mon Oct 08, 2012 11:21 am
by 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.
Posted: Mon Oct 08, 2012 5:05 pm
by 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?