Re: first N rows with a cursor

Mervyn at ifwdc.com <merv_at_ifwdc.com> wrote in message news:8ff7pn$1lg3$1_at_brimstone.medianet.ie...
> I don't know about the incompatibility, but you can easily achieve the
 same
> 10 rows in PL*SQL by declaring your cursor as the inner query only, and
 then
> loop 10 times around the fetch. That gives the same 10 rows.

Thanks for this suggestion, but what I'm really trying to do is return a cursor to a client program on a query like (this is pseudo-SQL -- I'm hoping to emulate TOP n using the rownum trick):

select x,y,z from
  (select top 10 a as x, b as y, c as z from t1 order by ...    union
   select top 10 p as x, q as y, r as z from t2 order by ...    union
   select top 10 e as x, f as y, g as z from t3 order by ...   )

Another case of interest arises from the need to scroll pagewise through a large list, directed by the remote user over the Web (so there is no state on any connection), where the 10th page might be accessed by the query:

select x,y,z from

   (select x,y,z from t1 order by ...)
   where rownum > 900 and rownum <= 1000

I wouldn't want to open the cursor on the inner query in the client, read 1000 rows and throw away the first 900. (It's bad enough to have to execute the inner query for each page in the server).

Jeff Received on Thu May 11 2000 - 00:00:00 CDT