It worked. Greatly appreciate your quick and right response Thanks a lot Mike.
On Sat, Aug 4, 2012 at 12:36 PM, Michael Moore <michaeljmo...@gmail.com>wrote: > first thing to do is to supply row numbers by partition. In your > example, a_prod, a_rx,a_units will be your partition. > after assigning row_numbers your data would look like this > > > InvoiceItem > > > pr a_numa_proda_rx a_unitsa_invoice 1 100prod0110 100020 1 101prod02 20 > 200020 2 102 prod02202000 20 > ClaimsItem > > > pr b_numb_prodb_rx b_unitsb_claim 1 45prod0110 100030 1 46prod02 20200030 2 > 47 prod02202000 30 > > > with this (pr) row_number in place, do like you already did but this time > include pr in your subquery join criteria. > > Here is how to add the partitioned row number ... > http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm > > Mike > > > > > On Sat, Aug 4, 2012 at 9:11 AM, Gayathri <gd0...@gmail.com> wrote: > >> Hi All, >> >> I need your help in the INSERT statement which is used during the >> migration from one system to another. >> >> Here is the details of table and data in source system - >> InvoiceItem >> >> >> a_num a_prod a_rx a_units a_invoice 100 prod01 10 1000 20 101 prod02 >> 20 2000 20 102 prod02 20 2000 20 >> ClaimsItem >> >> >> b_num b_prod b_rx b_units b_claim 45 prod01 10 1000 30 46 prod02 20 >> 2000 30 47 prod02 20 2000 30 >> My goal is to fill in the conversion table in the target system - CONV >> c_num >> c_invoice >> c_claim >> c_InoviceItem >> c_ClaimItem >> >> As of now the insert statement is as below - >> >> INSERT INTO CONV >> SELECT >> conv_seq.nextval, >> invoice_num_old, >> invoiceItem_num_old, >> *( select claimitem_num >> from claimitem ci >> where ci.b_prod = ii.a_prod >> and ci.b_rx = ii.a_rx >> and ci.b_unit = ii.a_unit >> and rownum = 1 >> ) claimitem_num_old* >> from >> invoiceitem ii, invoice i >> where ..... >> .... >> ; >> >> which is inserting records as below - >> >> conv_num c_invoice c_claim c_InvoiceItem C_ClaimItem 123 20 30 100 45 >> 124 20 30 101 *46* 125 20 30 102 *46* >> >> >> But I need to modified the insert statement to get c_claimitem as below - >> >> conv_num c_invoice c_claim c_InvoiceItem C_ClaimItem 123 20 30 100 45 >> 124 20 30 101 *46* 125 20 30 102 *47* >> >> Please let me know how can i associate the invoiceitem_num(a_num) to >> claimitem_num(b_num) to correspondingly match with each other taking care >> of the duplicates as well? >> >> Thanks in advance! >> >> G >> >> >> >> >> >> >> >> >> >> >> -- >> You received this message because you are subscribed to the Google >> Groups "Oracle PL/SQL" group. >> To post to this group, send email to Oracle-PLSQL@googlegroups.com >> To unsubscribe from this group, send email to >> oracle-plsql-unsubscr...@googlegroups.com >> For more options, visit this group at >> http://groups.google.com/group/Oracle-PLSQL?hl=en >> > > -- > You received this message because you are subscribed to the Google > Groups "Oracle PL/SQL" group. > To post to this group, send email to Oracle-PLSQL@googlegroups.com > To unsubscribe from this group, send email to > oracle-plsql-unsubscr...@googlegroups.com > For more options, visit this group at > http://groups.google.com/group/Oracle-PLSQL?hl=en > -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to Oracle-PLSQL@googlegroups.com To unsubscribe from this group, send email to oracle-plsql-unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
