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