Home » SQL & PL/SQL » SQL & PL/SQL » Compare data and insert unique data (merged)
Compare data and insert unique data (merged) [message #411979] Tue, 07 July 2009 06:36 Go to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Dear All

Please guide me,I have created the following code.

1-User input code through form 6i
2-on when button press trigger,system will get ro_make_cd,ro_fren_cd
from ro_fren_dtl table.(there is no colums of code in this table)
3-insert the user input code,ro_make_cd and ro_fren_cd into non_lts_dtl table.

By doing this there are lots of duplicates rows inserted in non_lts_dtl table ,Now I want to control duplication.

The system can ideally work by using the following scenario.

-User input the code like 'FFS'(by using form 6i)
-system extract ro_make_cd,ro_fren_cd from ro_fren_dtl(master table of column stated) by using cursor.
-system also extract all code,ro_make_cd and ro_fren_cd from non_lts_dtl table by using cursor.
-compare both data like
user input code(comes from form 6i),ro_fren_cd,ro_make_cd compare with code,ro_make_cd and ro_fren_cd(comes from non_lts_dtl)
and incase of duplication with the combination of code,ro_fren_cd,ro_make_cd discard it otherwise insert it into non_lts_dtl table.

Please advise how can i get my desired result.

My table structers are:

desc ro_fren_dtl
 RO_FREN_CD                                         VARCHAR2(5)
 RO_MAKE_CD                                         VARCHAR2(5)

desc non_lts_dtl
RO_FREN_CD                                         VARCHAR2(5)
RO_MAKE_CD                                         VARCHAR2(5)
CODE                                               VARCHAR2(6)




I am using the following code(this code insert data but not control duplication) to insert data in non_lts_dtl

DECLARE 
  CURSOR c1 IS 
    SELECT a.ro_fren_cd, 
           a.ro_make_cd, 
           :code 
    FROM   ro_fren_dtl a 
    WHERE  ro_fren_cd IN ('01','02','03','04'); 
   col_rec  c1%ROWTYPE; 
BEGIN 
  OPEN c1; 
   
  LOOP 
    FETCH c1 INTO col_rec; 
     
    EXIT WHEN c1%NOTFOUND; 
 
     
    INSERT INTO non_lts_dtl 
               (ro_fren_cd, 
                ro_make_cd, 
                code) 
    VALUES     (col_rec.ro_fren_cd, 
                col_rec.ro_make_cd, 
                :code); 
  END LOOP; 
   
  CLOSE c1; 
END; 
commit;

Re: Compare data and insert unique data (merged) [message #411998 is a reply to message #411979] Tue, 07 July 2009 07:13 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
you might find NOT EXISTS helpfull.
Re: Compare data and insert unique data (merged) [message #412168 is a reply to message #411998] Wed, 08 July 2009 01:49 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
Thanks for the reply,but i am unable to understand how can i use not exist.
Re: Compare data and insert unique data (merged) [message #412196 is a reply to message #411979] Wed, 08 July 2009 03:45 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use it in your cursor to check that a record doesn't already exist in non_lts_dtl with the same ro_fren_cd and ro_make_cd
Re: Compare data and insert unique data (merged) [message #412232 is a reply to message #411979] Wed, 08 July 2009 05:43 Go to previous messageGo to next message
gozuhair
Messages: 206
Registered: January 2008
Senior Member
The problem is that i am using form 6i for input code but i am unable to compare it with non_lts_dtl table.how can i compare it with non_lts_dtl table,please guide
Re: Compare data and insert unique data (merged) [message #412239 is a reply to message #411979] Wed, 08 July 2009 06:02 Go to previous messageGo to next message
Kaeluan
Messages: 179
Registered: May 2005
Location: Montreal, Quebec
Senior Member
Maybe you could add Unique Key constraint on your non_lts_dtl table to prevent duplication.

[Updated on: Wed, 08 July 2009 06:03]

Report message to a moderator

Re: Compare data and insert unique data (merged) [message #412339 is a reply to message #412232] Wed, 08 July 2009 14:37 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
gozuhair wrote on Wed, 08 July 2009 11:43
The problem is that i am using form 6i for input code but i am unable to compare it with non_lts_dtl table.how can i compare it with non_lts_dtl table,please guide


By using NOT EXISTS in the cursor.
Try it.

The fact that you're using forms is irrelevant.
Previous Topic: Regular expression
Next Topic: Help needed with update
Goto Forum:
  


Current Time: Fri Dec 09 19:17:12 CST 2016

Total time taken to generate the page: 0.12039 seconds