Re: Query returning results in different order

"Jeff Turner" <zigjst_at_hotmail.com> wrote in message news:c73aa58a.0207190539.7b3ad12d_at_posting.google.com...
> 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
> VAPPLICATIONS.TrialNo = VSTUDYACTIVEINGREDIENTS.TrialNo AND
> VTREATMENTS.TrialNo = 284 AND VSTUDYACTIVEINGREDIENTS.StudyNo = 121
>

And the 'order by' clause would be ..., er...., where, exactly?

Unless there's an order by, rows get returned in 'windspeed factor' order.

On alternate Thursdays.

Regards
HJR
>
> 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 Sat Jul 20 2002 - 03:39:56 CDT