Re: Request PL/SQL Help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 28 Jun 2001 14:22:37 -0700
Message-ID: <9hg76t0ddo@drn.newsguy.com>

In article <f98999c8.0106281244.2e88f73b_at_posting.google.com>, jc_va_at_hotmail.com says...
>
>I am trying to convert some data that previously had a non-unique
>index, and now a unique index is required on
>emplid,start_dt,sequence_nbr.
>
>I want to increment sequence_nbr (they're all 1) so I can create the
>unique index. My PL/SQL is a little rusty. Could someone take a look
>at what I've done and get me straightened out?
>
>I'd be grateful.
>
>
>declare
> hold_emp varchar2(11),
> seq_num integer(3),
> cursor c1 is SELECT
> EMPLID
> ,START_DT
> ,SEQUENCE_NBR
> ,COUNT(*)
> FROM PS_PRIORWORK_EXPER
> GROUP BY
> EMPLID
> ,START_DT
> ,SEQUENCE_NBR
> HAVING COUNT(*) > 1;
>begin
> for i in c1 loop
> if i.emplid = hold_emp then
> seq_num = seq_num + 1;
> dbms_sql(update ps_priorwork_exper up set sequence_nbr :=
>seq_num where up.emplid = c1.emplid and up.start_dt = c1.start_dt);
> else
> seq_num :=0;
> end if;
> hold_emp := c1.emplid;
> end loop;
>end;

Well, that PLSQL is a tad off but no worries - you don't need it

ops$tkyte_at_ORA817.US.ORACLE.COM> select * from t;

    EMPLID START_DT SEQUENCE_NBR

---------- --------- ------------
         1 28-JUN-01            1
         1 28-JUN-01            1
         1 28-JUN-01            1
         1 28-JUN-01            1
         1 28-JUN-01            1
         1 28-JUN-01            1
         1 28-JUN-01            1
         1 28-JUN-01            1
         1 28-JUN-01            1
         1 28-JUN-01            1
         2 30-JUN-01            1
         2 30-JUN-01            1
         2 30-JUN-01            1
         2 30-JUN-01            1
         2 30-JUN-01            1
         3 01-JUL-01            1

16 rows selected.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> update t

  2     set sequence_nbr = ( select count(rowid)
  3                            from t t2
  4                           where t2.emplid = t.emplid
  5                             and t2.start_dt = t.start_dt
  6                             and t2.rowid <= t.rowid )
  7    where exists ( select NULL
  8                     from t t3
  9                    where t3.emplid = t.emplid
 10                      and t3.start_dt = t.start_dt
 11                      and t3.rowid <> t.rowid )

 12 /

15 rows updated.

ops$tkyte_at_ORA817.US.ORACLE.COM>
ops$tkyte_at_ORA817.US.ORACLE.COM> select * from t;

    EMPLID START_DT SEQUENCE_NBR

---------- --------- ------------
         1 28-JUN-01            1
         1 28-JUN-01            2
         1 28-JUN-01            3
         1 28-JUN-01            4
         1 28-JUN-01            5
         1 28-JUN-01            6
         1 28-JUN-01            7
         1 28-JUN-01            8
         1 28-JUN-01            9
         1 28-JUN-01           10
         2 30-JUN-01            1
         2 30-JUN-01            2
         2 30-JUN-01            3
         2 30-JUN-01            4
         2 30-JUN-01            5
         3 01-JUL-01            1

16 rows selected.

ops$tkyte_at_ORA817.US.ORACLE.COM>

a single update will do it for you. works best of course if emplid/start_dt are indexed.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Thu Jun 28 2001 - 16:22:37 CDT