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 AND 

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 - 15:39:51 CEST