Re: how to use a cursor.

Kyoichi Ozaki <k-ozaki_at_igs.inter-gate.co.jp> wrote:
: i am working on a web application(cgi) that access an oracle database.
: but i can't solve this one problem , so please help.
 

: i am running a RedHat Linux 6.2 with php-3.0.15 , oracle 8.0.5. and here
: is the
: situation:
 

: i have a search page, and i need to sort the result of a query into
: divided pages.
: and i searched the web to get this solution:
 

: > SELECT a,b FROM (SELECT a,b,ROWNUM AS r FROM t WHERE ROWNUM <= 5+10)
: > WHERE r>=5
 

: but with this, i can't use `order by' and also there's no guarantee that
: i get the same
: result everytime. so i looked in further to get this:
 

: 0 SELECT STATEMENT Optimizer=RULE
: 1 0 VIEW
: 2 1 COUNT (STOPKEY)
: 3 2 TABLE ACCESS (FULL) OF 't'
 

: but this example gives me no idea in how to exec the sql statement.
: (sorry for my
: ignorance) so could someone give me a more detailed example?
 

: like selecting from table t, the fields a,b,c and sorting by b and get
: 10 rows from the
: 5th row of the result ?

Hi!

Try this:

'select * from
  (select a.*,

          rownum r2
   from (select x.*,

                  rownum r1 from (select a_field, another_field
				   from	 a_table
				   where a_field > another_field
				   order by anytingyouwant) x) a 
			    where  r1 >= ' || in_start || ')
		 where r2 <= ' || in_number;

The idea is to isolate the innermost select with the order - this way the order of the selected rows is not effected when you apply conditions to the rownum-columns.

I don't know if this is the most effective solution, but it works. I can't help to think that there must be a solution out there with less overhead.

I have found that if you take away one nesting in this statement - that is: apply conditions to r1 and r2 on the same level - it will not work. Same applies if you try to put the condition on r1 together with the main select.

Do anoyne know of a better solution?

regards
oystein, norway Received on Fri Oct 06 2000 - 02:41:00 CDT