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

Thanks for all your suggestions, but I'm not trying to limit the result to 100.
I'm trying to stop the query if it's going to be > 100. This is subtley different - if it's very large (a few millions records) it might take 5 mins to even count the rows let alone return the result set - so is it possible to 'break out' if we already know it's going to be more than 100.

The VARRAY was a good idea - but unfortunately the cursor get's the whole set when opened - ie. it opens 3 millions records before knowing it's too big.

Thomas Gaines <tanguero_at_pcisys.net> wrote in message news:3A272919.E990F441_at_pcisys.net...
> 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 Fri Dec 01 2000 - 04:18:48 CST