Re: [htmltmpl] Help with Data Pagination

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

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 <ma...@MI...> 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...
> > > >
> >
> >https://lists.sourceforge.net/lists/listinfo/html-template-users
> > > >
> > >
> > >
> > >__________________________________________________
> > >Do You Yahoo!?
> > >Tired of spam?  Yahoo! Mail has the best spam
> > protection around
> > >http://mail.yahoo.com
> > >
> > >
> >
> >-------------------------------------------------------
> > >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...
> >
> >https://lists.sourceforge.net/lists/listinfo/html-template-users
> >
> >
>
>
>__________________________________________________
>Do You Yahoo!?
>Tired of spam?  Yahoo! Mail has the best spam protection around
>http://mail.yahoo.com