Jump to content


Photo
- - - - -

Limiting records in a list box


7 replies to this topic

#1 Frank Coppola

Frank Coppola

    Member

  • Members
  • PipPip
  • 11 posts
  • Gender:Male
  • Location:Red Bank NJ, United States

Posted 08 March 2006 - 03:52 PM

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

#2 Anthony Hood

Anthony Hood

    Newbie

  • Members
  • Pip
  • 6 posts
  • Gender:Male
  • Location:Colchester, United Kingdom

Posted 09 March 2006 - 01:34 PM

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.


#3 Frank Coppola

Frank Coppola

    Member

  • Members
  • PipPip
  • 11 posts
  • Gender:Male
  • Location:Red Bank NJ, United States

Posted 09 March 2006 - 03:01 PM

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

#4 George Macken

George Macken

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 248 posts
  • Gender:Male
  • Location:Co. Wicklow, Ireland

Posted 09 March 2006 - 06:49 PM

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

#5 Wayne Biggs

Wayne Biggs

    Newbie

  • Members
  • Pip
  • 6 posts
  • Gender:Male
  • Location:UK

Posted 10 March 2006 - 09:29 AM

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

#6 DARREN

DARREN

    ProIV Guru

  • Members
  • PipPipPipPipPip
  • 396 posts
  • Gender:Male
  • Location:Florida,USA

Posted 10 March 2006 - 02:11 PM

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.
Things should be made as simple as possible, but not simpler

#7 Frank Coppola

Frank Coppola

    Member

  • Members
  • PipPip
  • 11 posts
  • Gender:Male
  • Location:Red Bank NJ, United States

Posted 10 March 2006 - 02:36 PM

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

#8 Frank Coppola

Frank Coppola

    Member

  • Members
  • PipPip
  • 11 posts
  • Gender:Male
  • Location:Red Bank NJ, United States

Posted 10 March 2006 - 02:49 PM

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



Reply to this topic



  


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users