Design dataflow that accounts for AND clause in Left Outer Join

Question

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.

Perhaps where clause and use things like `(T5.col2='Y' OR T5.col2 is null) AND (T5.Col3='1' OR t5.col3 is null)`? to make up for inablity to process ands on joins... However, this sounds like a serious tool limitation to me though. — xQbert, Jul 09 '14 at 14:04

score 0 · Accepted Answer · answered Jul 09 '14 at 14:23

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.

Thank you, I think this is an adequate solution to the problem. I wasn't aware that I could use conditional split for the AND values of joins. — Meta747, Jul 09 '14 at 17:21

Design dataflow that accounts for AND clause in Left Outer Join

1 Answers1