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

Pete -

Alex is mostly correct, but he is wrong about the rownum being applicable in Oracle8i only. I've used this conditional statement for both Oracle7 and Oracle8i with no problems whatsoever.

Also keep in mind that this addition to the query will not cause the statement to "bomb out" with an Oracle error, but simply stop delivering results once the condition fails. Try it for yourself to see what I mean.

Thanks,
Tom

Alex Filonov wrote:
>
> 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 - 22:29:13 CST