Toad World® Forums

Testing queries with an ORDER BY


#1

A user reported a problem testing the contents of a query that looked like this:

SELECT * FROM employees ORDER BY last_name

The problem is that we compare contents of queries using UNION and MINUS in a SELECT statement, and these set operators do not like ORDER BYs in their sub-queries.

So there are two things you can do if you run into this issue:

  1. Remove the ORDER BY: do you really need it? Generally the order of rows in your query are NOT significant when compared to another query. If you want to compare a query to a collection, however (which Code Tester supports), then order will be significant.

  2. If you cannot remove the ORDER By then change your query to:

SELECT * FROM (SELECT * FROM employees ORDER BY last_name)

or more generally

SELECT * FROM (your-query)

and then Code Tester will generate valid test code.