Re: How to get the 'top ten' from a SELECT count(*)

From: <sacq_at_usa.net>
Date: 2000/04/26
Message-ID: <8e7iin$r7r$1@nnrp1.deja.com>#1/1

In article <3906E272.A8F5D230_at_tool42.com>,   Carsten Jacobs <carsten.jacobs_at_tool42.com> wrote:
> Sorry to ask you again but
>
> I can't create a view on it because I execute the select .. where
> rownum<11
> in a loop where another cursor delivers values I use in the mentioned
> select. I tried to
> create the view for each cycle in the outer cursor loop but it looked
> like that DDL Statements are
> not allowed in procedures.

DDL is allowed in procedures, but only by using the DBMS_SQL (dynamic SQL) package. You probably don't want to do that.

If you're doing this inside a stored procedure (or function, or package), why not just close the cursor after you fetch ten records? In other words, something like:
DECLARE
  CURSOR c1 IS

     select foo, bar
     from baz
     order by foo;

 ....
BEGIN
  OPEN c1;
  FOR i IN 1..10
  LOOP
    FETCH c1 INTO c1rec;
    ....
  END LOOP;
  CLOSE c1;
END; My PL/SQL syntax is a bit rusty, but you get the idea I'm sure.

HTH -bn

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Apr 26 2000 - 00:00:00 CDT