Re: scrolling through tables

You will never get results back when your where clause includes 'rownum > x' where x is greater than 1. The first row fetched is assigned rownum=1 (which evaluates to false on your where clause, obviously), then the next row fetched is REASSIGNED ROWNUM=1! Therefore, nothing will ever be returned.

Try the following:

select results.* from (
select rownum rn, mytable.* from
table mytable) results
where rn > 10 and rn < 20;

The statement inside the parens is equivalent to a view which you are aliasing as 'results'. The virtual 'view' is aliasing table as 'mytable'. This will return * from your table, plus a preceding column rn which is an alias for the rownum. Keep in mind the while you can include an additional where clause in the inner or outer select statements, you cannot include a usable order clause because rownum is assigned prior to sorting.

-Brendan

In article <6jbs4s$rl1$1_at_news2.xs4all.nl>,   "Herbert" <herbs_at_worldonline.nl> wrote:
>
> While building an application i am experiencing the following problem:
>
> I want to select an 'window' from a selection from a table, in order to
> scroll through a table with increments of say 10 records at a time. A very
> simplistic view to this problem is:
>
> select * from table where rownum >10 and rownum < 20;
>
> this however won't work according to the oracle docs.
>
> Anyone got a suggestion?
>
> herbert_at_veronica.nl
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Wed May 13 1998 - 15:36:47 CDT