Re: Search engine like behaviour for result sets?

From: Jerry Gitomer <jgitomer_at_hbsrx.com>
Date: Wed, 28 Jul 1999 09:33:35 -0400
Message-ID: <7nn0pk$li7$1@autumn.news.rcn.net>

Hi Ryan,

    You can do this using the MINUS operator in conjunction with WHERE rownum < n.

            SELECT * FROM <table> WHERE rownum < 21
             MINUS
             SELECT * FROM <table> WHERE rownum < 11;

.
As long as no one is adding rows to the table while a user is browsing it this will work. If a row is added while the user is browsing it may or may not be visible to the user. This is going to depend on where Oracle inserts the row. For example, if the user is now browsing the 9th block of the table and Oracle inserts a row in the first block the user won't see it.

    You could also build a temporary table with one entry per row consisting of the rowid and a sequence number. You could then set up a query that would select rows with rowids whose sequence number is between two values. If a row is added to the table after the temporary table is built the user won't see it.

    I am sure there are other ways of doing what you want, but these are the two I have used.

regards
Jerry Gitomer

RyanO_at_aclweb.com wrote in message
<7nmlqq$ljg$1_at_nnrp1.deja.com>...
>Hi All,
>
>We have a database of products in Oracle and we need to place
search
>functionality within the project now. To make the results
readable we
>need to display them a set at a time, such as a search engine
does.
>
>Our simple select procedure is as follows:
>
> Procedure RetrieveAllProducts(ProdCur OUT curProd) Is
> Begin
> open ProdCur for select * from Products;
> End RetrieveAllProducts;
>
>This works well with our environment (Active Server Pages are
used to
>call this function from NT Server).
>
>We now need to limit the result set so that we can 'window'
through
>them a page at a time. For example, we want to say 'use the
search
>term "joe" and give the tenth through twentieth records'. If a
user
>decides to see more then we issues the same query, but from the
>twentieth record onwards.
>
>We cannot guarantee the order in which the results are returned,
or
>that there is a column with an ID which we can use to filter.
>
>Any ideas?
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Wed Jul 28 1999 - 08:33:35 CDT