Home » Other » Training & Certification » Procedure
Procedure [message #262656] Tue, 28 August 2007 00:48 Go to next message
kmanioracle
Messages: 19
Registered: March 2007
Junior Member
Hi

Please help to write a procedure for below condition.

Take values from table A and insert in B & C

Insert table B if stud_id in table A has duplicate value(s)

Insert table C if stud_id in table A don’t have duplicate value(s)

Table A structure
stud_id number,
Other_fileds ….

Table B, C structure is same as A.

Thanks in advance.

Regards
Mani

Re: Procedure [message #262665 is a reply to message #262656] Tue, 28 August 2007 00:59 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
create a cursor using
select stud_id, count(*) cnt from table_A group by stud_id;

then fetch the cursor and if count = 1 then table c
else insert table table b




Re: Procedure [message #262679 is a reply to message #262656] Tue, 28 August 2007 01:14 Go to previous messageGo to next message
kmanioracle
Messages: 19
Registered: March 2007
Junior Member
Hi Muzahidul Islam

Thanks for your instant reply.

We have more columns in table A (like mark1, mark2, total and etc). Assume we have 100 columns in table A, so it will not be good if we use all columns in group by clause..

Note : Please use single cursor.

Regards,
Mani
Re: Procedure [message #262687 is a reply to message #262679] Tue, 28 August 2007 01:23 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
You will use single cursor using group by stud_id column.
Then you fetch the cursor and if the count is more than 1 then select all row from table a to table c like
insert into table_c values (select * from stud_id = v_stud_id)

Do You got my point?



Re: Procedure [message #262698 is a reply to message #262656] Tue, 28 August 2007 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (val integer);

Table created.

SQL> create table a (val integer);

Table created.

SQL> create table b (val integer);

Table created.

SQL> insert into t select trunc(dbms_random.value(1,5)) from dual connect by level <= 10;

10 rows created.

SQL> insert into t values (0);

1 row created.

SQL> select val, count(*) cnt from t group by val order by val;
       VAL        CNT
---------- ----------
         0          1
         1          1
         2          4
         3          1
         4          4

5 rows selected.

SQL> insert first
  2  when cnt = 1 then into a values (val)
  3  else into b values (val)
  4  ( select val, count(*) over (partition by val) cnt from t )
  5  /

11 rows created.

SQL> select val from a order by val;
       VAL
----------
         0
         1
         3

3 rows selected.

SQL> select val, count(*) cnt from b group by val order by val;
       VAL        CNT
---------- ----------
         2          4
         4          4

2 rows selected.

Regards
Michel
Re: Procedure [message #262738 is a reply to message #262698] Tue, 28 August 2007 03:34 Go to previous message
kmanioracle
Messages: 19
Registered: March 2007
Junior Member
Thank you very much Muzahidul Islam & Michel
Previous Topic: Certification
Next Topic: transpose a row
Goto Forum:
  


Current Time: Sun Dec 04 13:01:24 CST 2016

Total time taken to generate the page: 0.09571 seconds