Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Query returning results in different order
I'm having a heck of a time figuring this out and I'm hoping someone
can help.
I've got a SQL statement that I can run on two different Oracle servers with different results. The data returned is the same, however the order of the result set is different.
It returns correctly on an Oracle 8.1.6.0 server, but does not on 8.1.6.3.0. The database schemas & data are identical on both servers. Here is the SQL statement:
SELECT DISTINCT
VTREATMENTS.TREATMENTID AS "Treatment ID",
VAPPLICATIONS.APPLICATIONNUM AS "Application No",
VSTUDYACTIVEINGREDIENTS.ISOFINTEREST AS "AI Of Interest"
FROM
VTREATMENTS, VAPPLICATIONS, VSTUDYACTIVEINGREDIENTS
WHERE
VSTUDYACTIVEINGREDIENTS.ISOFINTEREST= 'Yes' AND
VTREATMENTS.TrialNo = VAPPLICATIONS.TrialNo AND VTREATMENTS.TrialNo = VSTUDYACTIVEINGREDIENTS.TrialNo AND VTREATMENTS.TreatmentNo = VAPPLICATIONS.TreatmentNo ANDVAPPLICATIONS.TrialNo = VSTUDYACTIVEINGREDIENTS.TrialNo AND VTREATMENTS.TrialNo = 284 AND VSTUDYACTIVEINGREDIENTS.StudyNo = 121
The statement is generated by an application and I have no control over it (iow I can't just tack on an ORDER BY to it), but I can control the order of the views it is selecting from. I also have no control over changing the version of the Oracle server.
The funny thing about this is if I remove the first WHERE clause (ISOFINTEREST = 'Yes') it returns in the proper order on the 8.1.6.3 server!
any insight would be very appreciated.... thanks Received on Fri Jul 19 2002 - 08:39:51 CDT