Re: Search engine like behaviour for result sets?

RyanO_at_aclweb.com wrote:

> 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?
>

Well, this is how I solved a similar problem.

First, I created a table,

view_filters (
  viewid number,
  row_id rowid,
  row_no number
)

Next, I use an Oracle sequence to assign each query a viewid. Then I run the query and put its results into the view_filters table with something like this,

insert into view_filters
select <viewid>,rowid,rownum from (<qry>)

In my case order mattered and I'm using Oracle 7.3.4, so I couldn't nest 'order by' clauses. So I ended up using a stored procedure and the DBMS_SQL package to populate the table.

Then you can use the row_id and row_no columns to page through your query results like this,

select *
from tbl,view_filters
where (viewid=<viewid>) and

          (view_filters.row_id = tbl.rowid) and
          (view_filters.row_no between <n> and <m>)

For our intranet problem this seems to be working at an acceptable speed. Of course I'm open to better solutions too. Received on Wed Jul 28 1999 - 07:54:28 CDT