0

Thanks in advance for looking at this question!

This is all in the context of a FOR EACH loop which can get quite lengthy - think 100,000 records - and I'm looking for a way to take n records from a position in that resultset e.g. start @ 4000 & take the next 500 records.

I was looking around for keywords, in the ABL Reference, such as:

  • Position
  • LookAhead
  • RECID - Whether we can find a RECID at the nth position
  • Query Tuning

So far no luck. Any smarties out there with a hint?

Tom Bascom
  • 13,405
  • 2
  • 27
  • 33
karbonphyber
  • 156
  • 10

2 Answers2

3

Here is an example that I created against the sports database. The sports database is a sample database similar to the AdventureWorks database in SQL Server.

This should get you started:

def var v-query as char   no-undo.
def var h       as handle no-undo.

/* Here is where can set a dynamic query */
assign v-query = "for each orderline no-lock".

/* Create handle used for query */
create query h.

/* Set the table against the query so you can access it conveniently */
/* If you have other tables in your "for each", simply do a          */
/* set-buffers on each table */
h:set-buffers(buffer orderline:handle).

/* Prepare Query */
h:query-prepare(v-query).

/* DO Open */
h:query-open.

/* Setup query to row 10 */
h:reposition-to-row(10).

LINE_LOOP:
repeat:

   /* Read next row */
   h:get-next.

   /* Check if we are not past the end */
   if h:query-off-end then leave LINE_LOOP.

   /* Since we added orderline as a buffer we can now use it here */
   disp orderline.ordernum
        orderline.linenum
        orderline.itemnum
        orderline.price
        orderline.qty.

end. /* repeat */

h:query-close.

FYI, the Progress Knowledge base and the PSDN have great samples and tutorials

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
TerryB
  • 629
  • 1
  • 5
  • 13
0

other example - filling of dataset tables - BATCHING

DEFINE TEMP-TABLE ttOrder LIKE Order.
DEFINE DATASET dsOrder FOR ttOrder.

/* you can set a buffer object */
/*DEFINE DATA-SOURCE srcOrder FOR Order.*/

/* or you can set a query */
DEFINE QUERY qOrder FOR Order SCROLLING.
QUERY qOrder:QUERY-PREPARE ("FOR EACH Order").
DEFINE DATA-SOURCE srcOrder FOR QUERY qOrder.


BUFFER ttOrder:ATTACH-DATA-SOURCE( DATA-SOURCE srcOrder:HANDLE ).

/*The maximum number of ProDataSet temp-table rows to retrieve in each FILL operation.*/ 
BUFFER ttOrder:BATCH-SIZE = 10. 

/* Empties the table before the FILL operation begins. 
Without setting this attribute will next rows append to existing in temp-table */
BUFFER ttOrder:FILL-MODE = "EMPTY".  

/* first time - result 1 - 10 */
DATASET dsOrder:FILL ().

FOR EACH ttOrder:
  DISPLAY ttOrder.ordernum.
END.  

/* set the startpoint to position 11 */
DATA-SOURCE srcOrder:RESTART-ROW = 11.

/* second time 11 - 20 */
DATASET dsOrder:FILL ().

FOR EACH ttOrder:
  DISPLAY ttOrder.ordernum.
END.
firhang
  • 244
  • 2
  • 11