Same query but returned different records
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Same query but returned different records

  1. #1
    Join Date
    Apr 2002
    Posts
    73

    Same query but returned different records

    I ran the following query through sqplus and got 109 records returned, I login to another session and ran the same query but got 47 records returned. If I used the distinct for the select clause, I will get 47 records for both sessions. I like to know what causing the inconsistent reponse from the same query.

    SELECT /*+ RULE */
    old_tl.document_id,
    old_e.top_level_element_id,
    old_e.element_state_id,
    old_e.element_id AS source_element_id
    FROM
    element old_e, top_level_element old_tl
    WHERE
    old_tl.document_id=6488 and
    old_tl.responder_document_id = 42896 AND
    old_e.top_level_element_id = old_tl.element_id AND
    old_e.element_state_id IN (6, 7)
    ORDER BY old_e.source_element_id;

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Sounds like a bug ... what happens if you take off the RULE hint?
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Are you 100% sure that you are connecting to the same db each time?

    [blush] I once worked a full day on the wrong db. Didn't notice till I found I'd "lost" all the changes the next day. [/blush]

  4. #4
    Join Date
    Apr 2002
    Posts
    73
    Yes, I am pretty sure I'm connecting to the same schema of the same database. That problem only happened when we migrated to the Oracle 9i ( 9.2.0.1.0) and I'm thinking is that the bug related or not.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I suspect "IN" clause.
    Remove this line old_e.element_state_id IN (6, 7)
    And try again.

    Tamil

Posting Permissions

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



Click Here to Expand Forum to Full Width