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_rxa_unitsa_invoice 1 100prod01101000201 101prod02202000202 102prod0220200020 ClaimsItem pr b_numb_prodb_rxb_unitsb_claim1 45prod01101000301 46prod02202000302 47 prod0220200030 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
