If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

------- STRANGE RESULT ------------------
--
-- What's strange is that at first these two rows join successfully
-- into one row. But then the data from resultset B is
-- duplicated into its own row with resultset A values set to
-- null. I would never have guessed that one row on the
-- right-hand side could show up twice. Enough talk, here's
-- the result (two rows):

If you look very carefully at the second row, you'll see that not only is the row duplicated data, but one field is missing and did not get duplicated at all! And that is field b4. All the other fields in B were duplicated. What gives?

Some details:
- The join really is against two subsets (sub-queries, not tables).
- The actual 4th data item in both subsets came from the ROW_NUMBER() function applied over a PARTITION in the queried data.
- I'm using Oracle 9i.
- This duplication of data happens on other rows as well, but not always.
- I am certain that no other data matching the above join criteria exist in table A or B other than what I've shown here.

So to summarize my question:

1) Why is a row from the B side being duplicated?
2) When it is duplicated why is one field missing from the B side?

I'll give the query below. Here's a bit of background: In our business we have events (timestamps) that are happening each day of the week. Our analysts want the events sequenced 1st, 2nd, 3rd, etc. for each day of the week. Then the first events from each day are lined up together. Then the second events are lined up together, etc. Lastly, the various days do not always have the same number of events but we have to get them together anyway and leave blanks where there are no events (thus the FULL OUTER JOIN).

So here I am working on queries for Monday and Tuesday data (for now). I join Mondays data with Tuesdays data by linking origin and destination organization numbers, destination sort types, and the sequence number generated with the ROW_NUMBER() function over a PARTITION.

The data is too big to give here. But I can tell you that I've done both Monday and Tuesdays sub-queries, and the two example rows of data I showed previously are exclusively matching results (nothing else matches those two rows using the join condition). These aren't the only data that come out of the subqueries. But they are an exact match between Monday and Tuesday that should result in only one row. With some of the other data I also see this odd duplicating behavior.

It was a long post and so it would be easy to miss it, but I was trying to say that the Tuesday subselect only returns one row. If you go back and look at the post again you can see the exact row that is returned for Monday and Tuesday. I ran both subselects individually, and they *always* returned sequence numbers (the 4th field).