Limiting records in a list box
Posted 08 March 2006 - 03:52 PM
Any help would be appreciated.
Posted 09 March 2006 - 01:34 PM
You could do something like this in default logic..
SELECT FROM table
WHERE ((:$FIRST_500 = 'Y' AND ROWNUM <= 500) OR (:$FIRST_500 = 'N'))
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.
Posted 09 March 2006 - 03:01 PM
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.
Posted 09 March 2006 - 06:49 PM
maybe restructure your window / paging area - the following might be possible
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 !!!
Posted 10 March 2006 - 09:29 AM
You could use an inline view to select specific records in blocks of whatever size you require
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!
Posted 10 March 2006 - 02:11 PM
Posted 10 March 2006 - 02:36 PM
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.
Reply to this topic
0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users