Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: Full Outer Join of A & B tables duplicates data from B side.

    I'm going to skip the details for the two subqueries but here's the gist of it:

    ------ THE QUERY --------------
    Select a1, b1, a2, b2, .... a5, b5

    from (... resultset A)

    FULL OUTER JOIN

    (... resultset B)

    On a1 = b1 AND
    a2 = b2 AND
    a3 = b3 AND
    a4 = b4

    ------- ROW FROM TABLE A ---------------
    '4009 ', '4790 ', 'P', 1, '10/26/2004 12:27:16 AM'


    ------- ROW FROM TABLE B ---------------
    '4009 ', '4790 ', 'P', 1, '10/27/2004 3:56:33 AM'


    ------- 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):

    '4009 ', '4009 ', '4790 ', '4790 ', 'P', 'P', 1, 1, '10/26/2004 12:27:16 AM', '10/27/2004 3:56:33 AM'

    , '4009 ', , '4790 ', , 'P', , , , '10/27/2004 3:56:33 AM'

    -------- DONE ---------------------------------------

    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 hope someone can clarify this. I am really stumped on this one.
    Last edited by tamills; 11-08-04 at 13:37.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    I think we'd need to see the real tables and queries to try to understand this. On the face of it, either you have made a mistake somewhere, or Oracle has a bug that you need to report.

  3. #3
    Join Date
    Nov 2004
    Posts
    4
    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.

    I'll repeat the two rows here for completeness:

    Monday's example resultset row:
    '4009 ', '4790 ', 'P', 1, '10/26/2004 12:27:16 AM'

    Tuesday's matching example resultset row:
    '4009 ', '4790 ', 'P', 1, '10/27/2004 3:56:33 AM'

    Nothing else in either subquery matches [4009,4790,4,1] on the join condition.

    First row result after the full outer join:
    '4009 ', '4009 ', '4790 ', '4790 ', 'P', 'P', 1, 1, '10/26/2004 12:27:16 AM', '10/27/2004 3:56:33 AM'

    Second row result after the full outer join:
    , '4009 ', , '4790 ', , 'P', , , , '10/27/2004 3:56:33 AM'

    And here's the query:
    -----------------------------------------------
    Code:
    SELECT morg, torg, 
           mdest, tdest, 
           mds, tds, 
           mon_seq_nr, tue_seq_nr, 
           mon_ts, tue_ts
    from (SELECT LD_ORG_OGZ_NR as morg, 
          LD_DTN_OGZ_NR as mdest,
          LD_DTN_SRT_TYP_CD as mds,
          LST_ULO_SN_TS as mon_ts,
          ROW_NUMBER() OVER (PARTITION BY LD_ORG_OGZ_NR, 
                                          LD_DTN_OGZ_NR, 
                                          LD_DTN_SRT_TYP_CD
                             ORDER BY LST_ULO_SN_TS ASC) mon_seq_nr
     
          from TFPFCOB, TFPDATE
          where WND_DT = TO_DATE('10-30-04', 'mm-dd-yy')
            and TFPFCOB.RPT_OP_DT = TFPDATE.RPT_OP_DT
            and LD_ORG_OGZ_NR = '4009 '
            and TO_CHAR(TFPFCOB.RPT_OP_DT,'D')=  2
            and LST_ULO_SN_TS is not null
    
          order by LD_ORG_OGZ_NR, LD_DTN_OGZ_NR, 
                   LD_DTN_SRT_TYP_CD, LST_ULO_SN_TS)
    
         FULL OUTER JOIN
    
         (SELECT LD_ORG_OGZ_NR as torg, 
                 LD_DTN_OGZ_NR as tdest,
                 LD_DTN_SRT_TYP_CD as tds,
                 LST_ULO_SN_TS as tue_ts,
                 ROW_NUMBER() OVER (PARTITION BY LD_ORG_OGZ_NR, 
                                                 LD_DTN_OGZ_NR, 
                                                 LD_DTN_SRT_TYP_CD
                                    ORDER BY LST_ULO_SN_TS ASC) as tue_seq_nr
     
          from TFPFCOB, TFPDATE
          where WND_DT = TO_DATE('10-30-04', 'mm-dd-yy')
            and TFPFCOB.RPT_OP_DT = TFPDATE.RPT_OP_DT
            and LD_ORG_OGZ_NR = '4009 '
            and TO_CHAR(TFPFCOB.RPT_OP_DT,'D')=  3
            and LST_ULO_SN_TS is not null
    
          order by LD_ORG_OGZ_NR, LD_DTN_OGZ_NR, 
                   LD_DTN_SRT_TYP_CD, LST_ULO_SN_TS)
    
    
         on mORG = torg AND    
            mdest = tDEST AND
            mDS = tds AND         
            mon_seq_nr = tue_seq_nr
    Thanks for looking at this.
    Last edited by tamills; 11-09-04 at 10:58.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by tamills

    Monday's example resultset row:
    '4009 ', '4790 ', 'P', 1, '10/26/2004 12:27:16 AM'

    Tuesday's matching example resultset row:
    '4009 ', '4790 ', 'P', 1, '10/27/2004 3:56:33 AM'

    Nothing else in either subquery matches [4009,4790,4,1] on the join condition.

    First row result after the full outer join:
    '4009 ', '4009 ', '4790 ', '4790 ', 'P', 'P', 1, 1, '10/26/2004 12:27:16 AM', '10/27/2004 3:56:33 AM'

    Second row result after the full outer join:
    , '4009 ', , '4790 ', , 'P', , , , '10/27/2004 3:56:33 AM'

    These results seem to be perfectly normal for the case where your second subselect (the one for Tuesday) returns these two rows:

    '4009 ', '4790 ', 'P', 1, '10/27/2004 3:56:33 AM'
    '4009 ', '4790 ', 'P', , '10/27/2004 3:56:33 AM'

    As to why is this happening, I can't tell. You may want to run that subselect separately and see what kind of data it returns.

  5. #5
    Join Date
    Nov 2004
    Posts
    4
    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).

  6. #6
    Join Date
    Nov 2004
    Posts
    4
    So far, folks here at work have not been able to find a problem with my query. It's beginning to look like a bug. Any confirmation here?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •