I 'm still new to SapDB so maybe this is not applicable but if
you can afford having one search_result table for each
distinct query you can make it like this:
Search_id
Cust_id
Cust_name --> with an index on this column, of course
Do the insert like this:
insert into search_result
select <some_id>,
cust_id,
upper(cust_name)
from customer
where upper(cust_name) like 'LA%'
And then query by blocks:
SELECT cust_id FROM
(SELECT cust_id, rownum
FROM search_result
WHERE search_id = ?
ORDER BY cust_name)
WHERE rownum between ? AND ?
BTW, I 've seen the issue of iterating and paginating over
query results discussed many times on the
J2EE mailing lists. Maybe a quick search in servlet-interest
or j2ee-interest lists can give you more ideas.
Regards,
Martin
-----Original Message-----
From: PUB: Gert van der Laan [mailto:g.van.der.laan@stripped]
Sent: Thursday, February 20, 2003 5:24 PM
To: sapdb.general@stripped
Subject: store result of ordered select query, any ideas?
I am working on a small application with a SAP 7.4.3 with JBoss as
application server. In the applicationserver I want a stateless session
bean to perform searches. The result of search action can be ordered
(most likely it is) and will be requested in blocks.
Because the search action's query is costly I want it to perform only
once. For that the result needs to be stored in a table, my idea was to
create a search_result table with three columns:
search_id integer --> distinguish different queries
rslt_idx integer --> ordering information
rec_key integer --> key of record in real table
The search query fills the search_result table and different requests
get information from it.
The problem is how to fill the table. If, for example, I have a customer
table with the next two columns:
cust_id integer
cust_name varchar(50)
and suppose my search action consists of selecting all customers with
cust_name like 'la%' ordered by cust_name.
How do I fill the result table?
I thought off:
1. An insert statement with a query:
insert into search_result
select <some_id>
, rowno
, cust_id
from customer
where upper( cust_name) like 'LA%'
order by cust_name
But I'm not allowed to use the "order by" like this.
2. A procedure with code snippet like:
SELECT cust_id
FROM UZBADMIN.customer
WHERE UPPER( cust_name) LIKE 'LA%'
ORDER BY cust_name;
WHILE $RC = 0 DO
BEGIN
FETCH INTO :SLEUTEL;
SET ROWCOUNT = ROWCOUNT + 1;
INSERT INTO UZBADMIN.search_result
VALUES( 123, :ROWCOUNT, :SLEUTEL);
END;
The resultset has 41 records, but the procedure above takes a long time
and produces 128.000 records!
Any ideas??
Thanks
Gert van der Laan
_______________________________________________
sapdb.general mailing list
sapdb.general@stripped
http://listserv.sap.com/mailman/listinfo/sapdb.general
