7 replies to this topic

[Frank Coppola]

Was wondering if anyone knows a clever way to limit the number of records returned to a list box. We have some very large Oracle tables. If the user enters too broad a search they may be pulling thousands of records from the Oracle table. This takes forever to load into the list box. What we would like to do is pull a let's say 500 hundred records at a time then enable an icon to let the user know there are more records available. VIP does this when you export a large number of objects I am not sure where or how this is done.

Any help would be appreciated.

Frank

[Anthony Hood]

Are you using embedded SQL to build the list of records?

You could do something like this in default logic..

SQL
SELECT FROM table
WHERE ((:$FIRST_500 = 'Y' AND ROWNUM <= 500) OR (:$FIRST_500 = 'N'))
ENDSQL

Set the $FIRST_500 flag to 'Y' initially (prior to entering the LS), and then have a button which toggles the value between Y & N for limited list/full list. Then you'll need to do something with forcing redisplay.

Edited by Anthony Hood, 09 March 2006 - 01:35 PM.

[Frank Coppola]

Thanks Anthony for your reply. However I did try that previously and it works if you are selecting records in key order. If you do any kind of order by then you can actually be missing records because the ROWNUM is assigned before the reordering.

Eaxmple:

Let's say the key is INVOICE NUMBER if the list box is selecting records ordered by invoice number ROWNUM works fine. But if
you select by SHIP DATE. ORDER BY SHIP DATE it will seelct the first 500 records that meet the criteria. leaving out other invoices that may fall somewhere in the range but have a higer invoice number.

This is exactly what happened at one customer site. The customer was complaining that not all the records were being displayed. We realized that ROWNUM was assigned before the orderby.

I was hoping there was someway to control the data in PRO-IV.

Thanks again,

Frank

[George Macken]

Hi

maybe restructure your window / paging area - the following might be possible

Main LS

subls1 - clear workfile1

subls2 - clear workfile2

subls3 - an lsubpdate to read thru the file and build workfile the 2 workfiles - add 500 records - if a 501 record found then set a flag and LSEXIT

subls4 - the paging area displaying the contents of the workfile - entry lgc to this ls display a MSG "displaying 500 - other records are present"

after this ls allow/control user navigate back to subls3 and in ls3 read the next 500 -
you dont add to workfile 1&2 if the record alredy in workfile 1 from an earlier pass thru of the ls

hth - god bless the user who reads thru 500 records in a paging screen !!!

George

[Wayne Biggs]

Hi,

You could use an inline view to select specific records in blocks of whatever size you require

Example: -

select * from (select rownum myrow, a.* from mytable a)

where myrow between 100 and 200

You can also maintain the key ordering or specify a different 'order by' clause within the inline view although you will then need to add an 'order by myrow' to the outer query too.

This depends on the amount of data in the table of course but you could consider dumping your keys to a global temporary table and selecting from that!

hth,

Wayne

[DARREN]

Selection cycles behave in exactly the same way as updates (exept they stack and push data out upon termination). You could therefore introduce a counter in the after read of the Primary file that lsexit'ed after say 500 records. What would be even smarter is if you did the above - and the wrote the key of the 501 record to a temporary (memory) file, keyed on say PAGE_NUMBER (which you could increment). You could therefore read the page number for the page the user is on, get the start key and then sel-range, reading 500 (or whatever) records at a time.

[Frank Coppola]

Thanks everyone for your input. Darren your solution was exactly what I was hoping to find. It worked exactly the way I wanted.
When I get to the 501 record I save the key then enable a next Icon and lsexit. I also saved my start range to create a previous icon. It worked just as I hoped without the need for temp files.

Thanks
Frank

[Frank Coppola]

Oops I was hopeful that Darren's solution would work. The Lsexit exited the selection cycle but did not drop the records in the list box. What did I miss doing? Darren any thoughts?

Frank