store result of ordered select query, any ideas?

[Gert van der Laan]

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
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general