Re: [htmltmpl] Help with Data Pagination

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

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 <ma...@MI...> 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 <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...
> 
=== message truncated ===

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 