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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
