[SOLVED] Design dataflow that accounts for AND clause in Left Outer Join

Design dataflow that accounts for AND clause in Left Outer Join

I have a query that I am putting through SSIS merge join (left join). It joins only on the columns specified in the ON. What do I do for it to consider other columns with AND?

SELECT Col1, Col2
FROM Table1
... many joins..
LEFT JOIN Table5 T5 ON T5.Col1 = T1.Col1
                    AND T5.Col2 = 'Y'
                    AND T5.Col3 = '1'
                    AND T5.Col4 <= T1.Col2
LEFT JOIN Table6 T6 ON T6.Col1 = T5.Col5
                    AND T6.Col2 LIKE '%DD%

Is this not possible to do with Merge-join? What are other ways that can be used to solve this? I could just put the whole query through a execute SQL but that's not what I am looking to do.

Solution

Just going by the conditions that you have in your question.

AND T5.Col2 = 'Y'
AND T5.Col3 = '1'
AND T6.Col2 LIKE '%DD%

These are easy to implement using a conditional split connected to the data coming from T5/T6 and then connecting the output of the conditional split to the Left join input of the Merge Join.

AND T5.Col4 <= T1.Col2

This however is a bit more trickier since you cannot do a range comparison in the Merge join. The only way to accomplish this without resorting to a lookup logic on T5 is by skipping this condition when you do the merge join and connect the output of the Merge join to a conditional split again and do the comparison between the columns there to filter out unwanted rows.