Merge join

Hi, folks:
I am now testing merge join with results not expected.
I have two OLE data source (one from Oracle, one from SQLSERVER)
then I do a merge join with the key LOCATION_ID

OLE DATA SOURCE 1 OLE DATA SOURCE 2
-- --

Location_id volume location_id shortname

123 3.5 256 well_far_away
256 4.6

My expected result for my inner join is
256 4.6 well_far_away
But I got no rows

If I change it to full outer join
I got this

123 3.5 null
256 4.6 null
null null well_far_away
What did I do wrong and should I do a merge join like this?

Well from the outer join results it appears that mergejoin is not detecting that the location_id from data source 2 matches either of the rows on data source 1. Can you add the location_id from data source 2 to the output (for testing purposes only, of course) and see what the value is displayed as? You could also put a data viewer on the path from data source 2 to the mergejoin and see what the value coming into the mergejoin is. Looking at this I would guess that the location_id from data source 2 is not what you are expecting, but the only way to know is to look at it.

HTH,
Matt

|||

A simple Merge Join from two OLE-DB sources with the data you describe works fine for me. Is that really your data, if not try it, since it does work. Then described what you are doing in more detail perahps.

A misunderstaning I've seen on the forums and in person several times this week is the IsSorted and SortKey properties. If you set the sort properties by hand, and the data is not sorted then this will fail. Setting the properties does not induce a sort operation, it just describes how the data has already been sorted within the component. For SQL sources, make sure your ORDER BY clause matches the properties.

|||You guys are wonderful, I used the data viewer to look at the OLE source 1 and realized that even though I set the issorted=1, I did not have a order by clause in my SQL statement. Excaltly like what DarrenSQLIS said.I put in my order by clause and it works as expected. BTW, I am going to Vancouver next week attend a 4-day training on SQL server 2005, anyone else going.