Re: SQL/SQL*Plus: How to add numbers to rows?

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/10/17
Message-ID: <3448146A.3DC4@iol.ie>#1/1

Alex Jentilucci wrote:
>
> Pretty easy Steve;

Pretty sloppy, Alex.
The original posting pointed out why this wouldn't work.

>
> Here's an example:
> SELECT DISTINCT
> ROWNUM,
> TABLE_NAME
> FROM all_tables;
>
> ROWNUM will neatly number all rows returned.
>
> Alex
>
> Steve Frampton <3srf_at_qlink.queensu.FOOBAR.ca> wrote in article
> <6261al$b4q_at_knot.queensu.ca>...
> > [From: header modified to thwart e-mail spam. See .sig for details]
> >
> > I've often wondered about how to accomplish something like this using
> > SQL or SQL*Plus:
> >
> > Affiliation Employee Name
> > ----------- ----------------------------
> > AAW 1. Joe Smith
> > 2. Henry Lee
> >
> > Teamsters 1. Ralph Johnson
> > 2. Norman Frank
> > 3. Louis Short
> > 4. Jane Doe
> >
> > (I'm talking about the numeric entries). I've tried a myriad of
> > things including sequences (I get a "sequence not allowed here"
> > error), rownum (I get stupid unordered rows because I am joining
> > a number of tables and sorting data such as affilation codes and
> > surnames).
> >
> > How to display numeric lists? I'm looking for an Oracle7 SQL or SQL*Plus
> > solution.

Do you have any difficulty using a PL/SQL procedure to return your data? If not, you could very easily run such a procedure instead of a bare SQL statement:

declare

   cursor C is select ... order by ...
   seq number := 1;
begin

   for R in C
   loop

      dbms_output.put(seq);
      dbms_output.put(...);	-- your data
      dbms_output.new_line;
      seq := seq+1;

   end loop;
end;
/

Otherwise, I can't think of any way of returning a line number gainst an arbitrarily-sorted set.

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"

Received on Fri Oct 17 1997 - 00:00:00 CDT