OraFAQ Forum: SQL & PL/SQL » Pls help me its urgent

Home » SQL & PL/SQL » SQL & PL/SQL » Pls help me its urgent

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Pls help me its urgent [message #41259]

Thu, 12 December 2002 07:01

Vinod
Messages: 76
Registered: April 1999

Member

I have a column called description with values as follows

Admin Support
Admin Support
Admin Support
Air Support
Cable - Disaster Response
Cable - Operational Support
Cable - PMPP (Preparedness)
Cable - Program Support
Capacity Building
Capacity Building
Chemical, Biological, Radiological, & Nuclear
Commodity Procurement Contract
Communications
Computer Support
Contract Services (warehousing)
Coordination
Coordination
Disaster Assistance Response Program

I have another column in this table for which i have to update with the sequence nos. The update should happen in such a way that if the description is same then i have update with sequence.currval and if the description is different then i have to update with sequence.nextval for each row as follows

Admin Support----- 1
Admin Support----- 1
Admin Support----- 1
Air Support----- 2
Cable - Disaster Response----- 3
Cable - Operational Support----- 4
Cable - PMPP (Preparedness)----- 5
Cable - Program Support----- 6
Capacity Building----- 7
Capacity Building----- 7
Chemical, Biological, Radiological----- 8
Commodity Procurement Contract----- 9
Communications----- 10
Computer Support----- 11
Contract Services----- 12
Coordination----- 13
Coordination----- 13
Disaster Assistance Response----- 14

Pls help me how do i do this, this is very urgent

Thanks

Vinod

Report message to a moderator

Re: Pls help me its urgent [message #41261 is a reply to message #41259]

Thu, 12 December 2002 07:27

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Hi,
follow this example.
source table - emp
dummy view - dum
column which is sequenced - job
column where sequence is stored - seq

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL> alter table emp add(seq number);

Table altered.

SQL> ed
Wrote file afiedt.buf

  1  create or replace view dummy
  2  as
  3  select rownum r, job
  4* from (select job,count(*) from emp group by job)
SQL> /

View created.

SQL> update emp
  2     set seq=(select seq from dum
  3                   where emp.job=dum.job)
  4     where exists
  5             (select *
  6              from  dum
  7              where emp.job=dum.job);

14 rows updated.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO        SEQ
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20          2
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30          5
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30          5
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20          3
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30          5
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30          3
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10          3
      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20          1
      7839 KING       PRESIDENT            17-NOV-81       5000                    10          4
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30          5
      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20          2
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30          2
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20          1
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10          2

14 rows selected.

Report message to a moderator

Re: Pls help me its urgent [message #41264 is a reply to message #41261]

Thu, 12 December 2002 07:58

Vinod
Messages: 76
Registered: April 1999

Member

I did what u told as follows

create or replace view dum
as select rownum r, sector_description from (select sector_description,count(*)
from sector_lookup group by sector_description)
/

update sector_lookup set dummy=(select dummy from dum
where sector_lookup.sector_description =
dum.sector_description)where exists (select * from dum where sector_lookup.sector_description=
dum.sector_description)
/

It said 66 rows updated but there was null values

I also tried the same thing on emp, but the new seq column was updated with null values

pls help me

Thanks
Vinod

Report message to a moderator

Re: Pls help me its urgent [message #41267 is a reply to message #41261]

Thu, 12 December 2002 09:58

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

wierd.
post your steps with data(on emp).
did u commit?(actually not required, if from the same session).

Report message to a moderator

Re: Pls help me its urgent [message #41272 is a reply to message #41259]

Thu, 12 December 2002 11:27

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

I wouldn't recommend storing this sequence value in the table. It is better to use analytic functions at query time to calculate the value.

If you store the value, think about what happens when you add new rows to this table. You have to go look up what the sequence is for this description.

Instead, just:

sql>select description, dense_rank() over (order by description) seqval
  2    from t;
 
DESCRIPTION                                           SEQVAL
-------------------------------------------------- ---------
Admin Support                                              1
Admin Support                                              1
Admin Support                                              1
Air Support                                                2
Cable - Disaster Response                                  3
Cable - Operational Support                                4
Cable - PMPP (Preparedness)                                5
Cable - Program Support                                    6
Capacity Building                                          7
Capacity Building                                          7
Chemical, Biological, Radiological, & Nuclear              8
Commodity Procurement Contract                             9
Communications                                            10
Computer Support                                          11
Contract Services (warehousing)                           12
Coordination                                              13
Coordination                                              13
Disaster Assistance Response Program                      14

If you just have to store the value, the update can be done with:

update t t1
   set seq = (select seq
                from (select rowid, dense_rank() over (order by description) seq
                        from t) t2
               where t2.rowid = t1.rowid);

Report message to a moderator

Previous Topic:	Sequence question, pls help
Next Topic:	subquery

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue May 14 22:17:50 CDT 2024