on 08-21-2013 12:09 PM
Hi Experts,
I am having a requirement in my JDBC -> PI --> Proxy scenario, like in database PI need to fetch the data from two tables and need to update these fields into a single table of SAP through Proxy. Am not able to deside the approch how I need to develop the ESR part. Because am having 2 similar fields in table 1 and table 2, due to this am feeling difficulty to create the DT types by taking the duplicate names.
So please suggest me how I can handle this scenario by fetching data from 2 tables of database.
Thanks in Advance..!
--
With Warm Regrads
Raj
Hi Raj,
My recommendation would be to either handle it through stored procedure or get a staging table created in the database.
What will a stored procedure do:
It will retrieve the data from the 2 tables based on the foreign key relationship and PI will simply get the output which is a direct input to the SAP table.
What will the staging table do:
It will hold the record for the SAP table. Database team will populate this table with a batch job based on Select|Join (you can discuss the solution with them). PI will just poll this DB table and read the records. Can be done by Select query and Update flag.
This will facilitate a simple design.
Hope it answers your question.
Ambrish
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
You would need a BPM for this design. You would also need to work out how to merge data from the 2 tables. What is the relationship between the data in the 2 tables? 1:1 or 1:many?
Best design would be like I suggested earlier which will simplify the process and ensure data integrity.
Cheers,
Ambrish
Hi Ambarish,
As per your suggestion I have discussed with DB team and asked to create a single view for both tables. So now the scenario is PI need to fetch the data and need to delete the same data after extraction in Asynchronous mode.
Am using DELETE query in receiver JDBC to achive this.
Many thanks for your suggestion..!
--
With Warm Regards
Raj
Hi,
>>Because am having 2 similar fields in table 1 and table 2, due to this am feeling difficulty to create the DT types by taking the duplicate names.
Apart from above suggestion of using Stored Proc or Staging Table, if your only concern is with naming the DT fields in ESR, you can try to give alias for field name in Select query in the channel.
something like,
Select First_Name as FirstName, Last_Name, Date...
your DT can have field as FirstName instead of First_Name
did i understand your issue correctly?
Regards,
Zameer
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
94 | |
11 | |
11 | |
10 | |
9 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.