Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2012
    Posts
    2

    Unanswered: Data mismatching in Oracle 9i and 11i

    Data mismatching in Oracle 9i and 11i
    Query i have used in both 9i and 11i

    SELECT DISTINCT A.TOPIC_ID AS TOPIC_ID,
    A.TOPIC_DESC AS TOPIC
    FROM TOPIC A,USER C
    WHERE A.BSF_TRNSCTN_TYP_CD <> 'D'
    AND ( A.USER_ID_PRMRY = C.USER_ID
    OR A.USER_ID_SCNDRY = C.USER_ID

    )
    AND A.bsf_plnt_id = 1
    The above query will result data that have matching USER_ID in user table for either USER_ID_PRMRY or USER_ID_SCNDRY.

    In Oracle 9i it returns the correct result.


    But in Orcale 11i it returns if both USER_ID_PRMRY or USER_ID_SCNDRY are same.

    See the sample data and out puts
    USER_ID_PRMRY USER_ID_SCNDRY Result in 9i Result in 11i
    11 22 Yes No
    22 22 Yes Yes
    55 11 Yes No
    55 55 Yes Yes

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Please, post a complete test case (CREATE TABLE and INSERT INTO sample data) so that we could test it; it is difficult to guess with information you posted so far.

  3. #3
    Join Date
    Sep 2012
    Posts
    2
    Table : USER

    USER_ID | FNAME
    133667 | Bruce
    137243 | Dileep



    Table : TOPIC

    BSF_LOC_ID | LOC_DESC | USER_ID_PRMRY | USER_ID_SCNDRY | BSF_PLNT_ID,
    901 | hmm | 137243 | 133667 | 1
    902 | Hmm1 | 137243 | 137243 | 1
    903 | third | 133667 | 137243 | 1

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    post FORMATTED EXPLAIN PLAN from each system
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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