Re: giving tuples a number to make them unique..

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Fri, 15 Jan 1999 19:03:52 GMT
Message-ID: <36a090f6.149314@inet16.us.oracle.com>

On Thu, 14 Jan 1999 15:29:46 GMT, rosinowski_at_gmx.de (Jan Rosinowski) wrote:

>
>i need to number rows in a _select_-statement to get unique tuples
>with ascending instanceids:
>
>name, instanceid
>a, 1
>a, 2
>b, 1
>c, 1
>c, 2
>c, 3
>d, 3
>
>(the select-statement is a flattened inclusion-hierarchy using
>connect-by which is intended to be inserted in another relation)
>
>any ideas?

You could solve it with a function.

eg.

SQL> desc t

 Name                            Null?    Type
 ------------------------------- -------- ----
 A                                        VARCHAR2(1)

SQL> select * from t;

A
-
a
a
b
c
c
c
d

7 rows selected.

create or replace
package my_seq as
  function inst_id( p_a varchar2 ) return number;   pragma restrict_references( inst_id, WNDS ); end my_seq;
/

create or replace
package body my_seq as

  g_cnt number;
  g_value varchar2(1);

  function inst_id( p_a varchar2 ) return number is   begin
    if g_value <> p_a or g_value is null then dbms_output.put_line( p_a );

      g_cnt := 0;
      g_value := p_a;

    end if;
    g_cnt := g_cnt + 1;
    return g_cnt;
  end inst_id;

begin
  g_cnt := 0;
  g_value := null;
end my_seq;
/

SQL> select a name, my_seq.inst_id(a) instanceid from t order by a;

NAME INSTANCEID
---------- ----------

a                   1
a                   2
b                   1
c                   1
c                   2
c                   3
d                   1

7 rows selected.

Hope this helps.

chris.

>
>if that's not possible: how can i get a sequence on the fly? in sybase
>one uses number(*) for that purpose..
>
>ciao, jan

--
Christopher Beck
Oracle Corporation
clbeck_at_us.oracle.com
Reston, VA.

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri Jan 15 1999 - 13:03:52 CST