Daily Oracle

short Oracle take-away tips that you can put to use in under an hour.

Saturday, June 14, 2008

Ensuring that a sql query returns all or nothing

I have a little project which automatically returns the result from a sql query to an Excel worksheet.

I run this job recently and the user complained that the Excel output was unusual.

I did some investigation and found that the query did not run to completion. It bombed out midway because there was a division by zero error.

Because the job was automatic and it was run in Excel, I did not see the error message.

That got me thinking - if the output was empty, I would realize immediately that the something was wrong. When it returned an impartial set, only a user who analyzed can tell whether it was wrong or not.

So, I wanted the query to return all or nothing.

And that is quite easily done, just add an order by clause at the end of the query.

The order by clause will retrieve all the rows and then sort them. If it could not retrieve all the rows because some of the rows has, for example, a division by zero error, nothing will return. The query will then return all or nothing at all.

Technorati tags: ,

Links