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_e.element_id AS source_element_id
element old_e, top_level_element old_tl
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;
Sounds like a bug ... what happens if you take off the RULE hint?
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]
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 ( 188.8.131.52.0) and I'm thinking is that the bug related or not.
I suspect "IN" clause.
Remove this line old_e.element_state_id IN (6, 7)
And try again.
Click Here to Expand Forum to Full Width