Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: SELECT STATEMENT

Re: SELECT STATEMENT

From: Joseph Ranseth <jranseth_at_worldNO_SPAMcupfishing.com>
Date: Fri, 25 Aug 2000 14:17:31 -0500
Message-ID: <GBzp5.1666$Io1.13494@news1.mts.net>

Yes, my mistake. There should have been an outside query to that. Just make what I gave as a sub-query, putting the where condition on the parent query.
Should be more like this...
my_query := 'select * from (Select ROWNUM() as rank, fielda ....fieldx, FROM my table) WHERE rownum BETWEEN '||lbound||' AND '||ubound||' ORDER BY rownum';
Of course if you want your original query sorted, it will have to be inside of a sub-query as well.
hth (sorry for the confusion)

--
Joseph Ranseth - Webmaster
World Cup Fishing
http://www.worldcupfishing.com
"TurkBear" <johng_at_mm.com> wrote in message news:39a69fa7.6453379_at_news...

>
> Unless it is very different in PlSql
> rownum between x and y will fail if x is anything other than 1
>
> Each record returned is checked to see if it satisfies the where clause;
the
> first record is rownum = 1 , if it is not between x and y ( say x = 4 and
y=12)
> it will be rejected, and the next row will be retrieved - However, this
row
> will still be rownum = 1 and will also fail.......only selected records
> increment rownum so your lowvalue must be 1
>
>
>
> "Joseph Ranseth" <jranseth_at_worldNO_SPAMcupfishing.com> wrote:
>
> >Use WHERE ROWNUM BETWEEN x AND y to determine which rows to retrieve.
> >You might want to create a dynamic SQL statement, and pass the
> >boundaries.(x,y)
> >my_query varchar2(4000);
> > type l_ref_cursor is ref cursor;
> > my_ref_cursor l_ref_cursor;
> >
> >my_query := 'Select ROWNUM() as rank, fielda ....fieldx, FROM my table
WHERE
> >rownum BETWEEN '||lbound||' AND '||ubound||' ORDER BY rownum';
> >
> >OPEN my_ref_cursor FOR my_query LOOP
> > htp.prn( whatever values you want to rint);
> >END LOOP;
> >CLOSE my_ref_cursor;
> >
> >create your links at the bottom of the recordset as follows.
> >htp.prn('
> ><a href="my_page?lbound='|| x-10 ||'&ubound='||y-10 ||'">Prev 10</a>
> ><a href="my_page?lbound='|| x+ 10 ||'&ubound='|| y+10 ||'">Next 10</a>
> >');
> >This will requery the recordset each time, which I assume is what you
were
> >looking for.
> >Lemme know if that's not what you need, or if you need clarification.
> >hth
>
Received on Fri Aug 25 2000 - 14:17:31 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US