Skip to Content
Mar 05, 2022 at 04:49 AM

PI mapping query multiple records lookup


Hey All,

We are on PO system.

We need to do a mapping with multiple source tables from SystemA and one source table coming from SystemB - the target is multiple tables of SystemA with updated data from SystemB. The complexity is how to do lookup from SystemB records based on SystemA fields - what is the most efficient way to do it? There are 45 source tables from SystemA and 1 table from SystemB.

All the 45 tables are different, but they have some common primary key fields and some unique primary key fields in them. The SystemB table has all the data of these SystemA tables with these key fields and two other fields whose data needs to be updated in the SystemA tables.

Let say one table1 from SystemA has field1,field2,field3,field4,field5 as primary key and several other fields from field6 to field55.

System B table has SeqNumber as primary key, so it can have all the other fields like field1,field2,field3,field4,field5,field6,field7,field8,9,field10 as other fields.

Now the data of say field44 and field45 of table1 needs to be updated based on SystemB table field8 and field9.

Let say another table of SystemA has primary key as field2,3,4,5,8,9 and needs to update only one field field10 from SystemB table.

Similarly all the remaining 43 tables of SystemA have different combination of primary key fields in them.

What is the most efficient way to do this mapping? Thx.