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
Click the link below to see the new game I'm developing!
Limiting records in a list box
Started by
Frank Coppola
, Mar 08 2006 03:52 PM
7 replies to this topic
#2
Anthony Hood
-
- Members
-
- 6 posts
Newbie
- 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.
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
-
- Members
-
- 11 posts
Member
- 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
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
-
- Members
-
- 248 posts
ProIV Guru
- 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
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
-
- Members
-
- 6 posts
Newbie
- 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
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
-
- Members
-
- 396 posts
ProIV Guru
- 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
-
- Members
-
- 11 posts
Member
- 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
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
-
- Members
-
- 11 posts
Member
- 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
Frank
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users
Click the link below to see the new game I'm developing!
