Re: [htmltmpl] Help with Data Pagination

SourceForge Headquarters 225 Broadway Suite 1600 San Diego, CA 92101 +1 (858) 454-5900

In the asktom page you reference below, the query similar to mine is used 
as a example that WORKS (please notice RNUM, not ROWNUM in the WHERE 
clause.)

select *
from (
select p.*, rownum rnum
   from ( select * from big_table ) p
)
  where rnum between 90 and 100

I don't think it's productive to continue the discussion whether or why it 
should or should not work.  If anyone is still interested, they can try 
running this or use a table in their own schema:

select *
from (
select p.*, rownum rnum
   from ( select * from user_tab_columns
   ORDER BY column_name) p
)
  where rnum between 21 and 30
/

At 05:26 PM 1/30/2006 -0800, Bob Diss wrote:
>Again, please reference the Oracle documentation on
>the subject.  Also, you might find some helpful info
>on the ROWNUM pseudo-column at
>http://asktom.oracle.com, specifically
>http://asktom.oracle.com/pls/ask/f?p=4950:8:5024074411414130873::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:127412348064
>
>HTH!
>
>
>--- Marina Hauptman <marina@MIT.EDU> wrote:
>
> > As long as you are using RNUM column from the
> > subquery, you can collapse
> > the query into two levels.  It works for the same
> > reason RNUM works in
> > RNUM > nnn.  I did test it.
> >
> > At 11:17 AM 1/30/2006 -0800, Bob Diss wrote:
> > >Sorry, but this is not the case.  Because of the
> > way
> > >Oracle issues ROWNUM values, you can't collapse the
> > >query I presented into just two levels.  See the
> > >Oracle Database SQL Reference.  On online version
> > can
> > >be found at http://tahiti.oracle.com (registration
> > may
> > >be required?).  Quote: "Conditions testing for
> > ROWNUM
> > >values greater than a positive integer are always
> > >false."
> > >
> > >Essentially, Oracle assigns a ROWNUM to a row, and
> > >then increments ROWNUM, as it is output by the
> > WHERE
> > >clause.  This is why you have to do things in three
> > >stages.  If you filter ROWNUM as in this example
> > >("between 26 and 50") you'll discover you get
> > nothing.
> > >  This is because ROWNUM is never between 26 and 50
> > --
> > >each row output from the WHERE clause is actually
> > the
> > >ROWNUM=1, and since that never matches the clause,
> > >ROWNUM is never incremented.
> > >
> > >This is why the three levels are necessary.  The
> > >innermost query actually generates the rows in the
> > >sorted order.  Since the ROWNUM value is assigned
> > >before sorting, you can't do any filtering at this
> > >stage on ROWNUM (the sorted records are not in
> > ROWNUM
> > >order).  The middle strips off rows beyond your
> > page,
> > >and fixes the value of ROWNUM as an additional
> > column
> > >to the query.  You can filter on ROWNUM here
> > because
> > >you're asking for rows <= some value.  Finally, the
> > >outer query strips off the rows before the ones you
> > >want by filtering on the fixed-in-time value of
> > >ROWNUM.
> > >
> > >The problem with my original query comes from a
> > column
> > >ambiguity on the middle query.  Oracle gets
> > confused
> > >by the "*, rownum as rnum" portion of the query.
> > This
> > >can be resolved by giving the innermost select a
> > table
> > >alias as in:
> > >
> > >select * from (
> > >   select x.*, rownum as rnum from (
> > >     select * from a_table where a_clause order by
> > >a_clause
> > >   ) x where rownum <= 50
> > >) where rnum >= 26
> > >
> > >Thanks for pointing out the problem.
> > >
> > >As Marina said, where clause conditions are best
> > >placed in the inner query.  However, Oracle can
> > >sometimes (often?) transfer the conditions from the
> > >outer query down to the inner one.  Oracle calls
> > this
> > >"predicate push".
> > >
> > >--- Marina Hauptman <marina@MIT.EDU> wrote:
> > >
> > > > Regarding the query,
> > > > The same can be accomplished with 2 levels of
> > > > SELECT:
> > > > >   SELECT * FROM (
> > > > >     SELECT A_TABLE.*, ROWNUM AS RNUM FROM
> > A_TABLE
> > > > ORDER BY
> > > > >A_CLAUSE
> > > > >   ) WHERE RNUM BETWEEN  26 AND 50
> > > >
> > > > Also, at least on my Oracle installation I get
> > an
> > > > error when * is not
> > > > qualified (A_TABLE.*) in the innermost query.
> > > >
> > > > If there is a where clause it should apply to
> > the
> > > > innermost select to take
> > > > advantage of existing indexes.
> > > >
> > > >
> > > > At 12:23 PM 1/28/2006 -0800, you wrote:
> > > > >One common way to return a "page" of record
> > from
> > > > >Oracle is to use a nested query.  For example:
> > > > assume
> > > > >you display 25 records per page, and you wish
> > to
> > > > >retrieve page 2's records, your query would
> > look
> > > > >something like this:
> > > > >
> > > > >SELECT * FROM (
> > > > >   SELECT * FROM (
> > > > >     SELECT *, ROWNUM AS RNUM FROM A_TABLE
> > ORDER BY
> > > > >A_CLAUSE
> > > > >   ) WHERE ROWNUM <= 50
> > > > >) WHERE RNUM >= 26
> > > > >
> > > > >The inner-most SELECT retrieves the records
> > you're
> > > > >paging and sorts them in the appropriate order.
> > > > The
> > > > >middle SELECT trims off the records after the
> > ones
> > > > you
> > > > >want.  The outer SELECT trims off the records
> > > > before
> > > > >the ones you want.
> > > > >
> > > > >This three-step query is necessary in Oracle
> > > > because
> > > > >of the way the pseudo-column ROWNUM is assigned
> > > > >values.
> > > > >
> > > > >- Bob
> > > > >
> > > > >--- Philip Tellis <phi...@gm...>
> > wrote:
> > > > >
> > > > > > Sometime on Jan 27, PIXpDIaC cobbled
> > together
> > > > some
> > > > > > glyphs to say:
> > > > > >
> > > > > > > achieve pagination to display result sets
> > > > queried
> > > > > > from Oracle in
> > > > > > > multiple pages. Is there a plug-in for
> > HTML::
> > > > > > Template to achieve
> > > > > >
> > > > > > this isn't an HTML::Template problem, this
> > is an
> > > > SQL
> > > > > > problem.  Construct
> > > > > > your SQL to only return one page of data at
> > a
> > > > time
> > > > > > given a start and
> > > > > > count.  Not sure how to do it in Oracle, but
> > > > MySQL
> > > > > > has a non-standard
> > > > > > addition called LIMIT that is added to the
> > end
> > > > of
> > > > > > your SQL like this:
> > > > > >
> > > > > > LIMIT 31, 10   (get 10 records starting from
> > the
> > > > > > 31st)
> > > > > >
> > > > > > --
> > > > > > "Idiot I may be, but tied up I ain't."
> > > > > >          -- Gaspode the wonder dog
> > > > > >             (Terry Pratchett, Moving
> > Pictures)
> > > > > >
> > > > > >
> > > > > >
> > > >
> > >
> >
> >-------------------------------------------------------
> > > > > > This SF.net email is sponsored by: Splunk
> > Inc.
> > > > Do
> > > > > > you grep through log files
> > > > > > for problems?  Stop!  Download the new AJAX
> > > > search
> > > > > > engine that makes
> > > > > > searching your log files as easy as surfing
> > the
> > > > > > web.  DOWNLOAD SPLUNK!
> > > > > >
> > > >
> > >
> >
> >http://sel.as-us.falkag.net/sel?cmd=lnk&kid=103432&bid=230486&dat=121642
> > > > > >
> > _______________________________________________
> > > > > > Html-template-users mailing list
> > > > > > Htm...@li...
> >
>=== message truncated ===
>
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam?  Yahoo! Mail has the best spam protection around
>http://mail.yahoo.com