Re: How to 'stop counting' past 100 lines?

From: Alex Filonov <afilonov_at_pro-ns.net>
Date: Thu, 30 Nov 2000 20:24:25 GMT
Message-ID: <906d1l$l93$1@nnrp1.deja.com>

In article <975580401.15710.0.nnrp-13.d4e467b6_at_news.demon.co.uk>, "Pete Reeves" <pete.reeves@ spamnot wheatley-associates.co.uk> wrote:
> I'm trying to write an address searching routine, but if the search will
> return > 100 lines I want the user to refine the search.
> It works fine by doing a count before returning the results, but
considering
> we have 3 million records, the count takes too long - the 100 lines max is
> to prevent this time wasting.
>
> Question is, how can I get oracle to 'bomb out' if the count passes
100, ie.
> dont keep counting to 3 million becuase I want to return nothing to
the user
> and get them to refine the search.
>
> Thanks in advance.
> Pete Reeves.
>

If you don't have order by, add condition rownum <= 100. If you do have order by, use

select <select_list>
from
(<your select including order by>)
where rownum <= 100.

It works on 8.1 only.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 30 2000 - 14:24:25 CST