JDBC Sender

[Former Member]

Hi,

Requirement:-

Sender JDBC to SAP scenario.

I have three table - Header, Detail and Log table.

All the three table has SEQ_ID which is the oracle generated key and it maintains relation between all the three table.

Header has multiple detail records which is maintained in detail table.

requirement is to first get all the SEQ_ID from TR_LOG table where two fields of TR_LOG has value "SAP" and date manage field is null.

Once the SEQ_ID is fetched from TR_LOG I need to fetch the corresponding values from header table and detail table note that one header can have multiple detail values.

At the end of the fetching based on SEQ_ID i wanted to update the date manage field of TR_LOG table to current system date.

I managed to do following- get the SEQ_ID from TR_LOG and fetch the data from header table and update the date manage in TR_LOG but I am getting difficulty in fetching the data from detail records as it is a case of nested select.

Chirag

Edited by: Chirag Gohil on Nov 12, 2010 7:21 AM

[Former Member]

I have used join between three table tried in SQL editor need to test with JDBC adapter.

chirag

[baskar_gopalakrishnan2]

Hi,

This can be implemented without BPM.

Simple steps:

1)Write Query/Select Statement to retrieve IDs and use that to retrieve records for parent and child tables using join statements.

The above query can be easily written and test it in the database first. Then use it in PI (sender channel select statement field).

Note: When you join the tables, make sure to fine tune the query for the best performance with the help of DBA.

2) Date portion can be updated in the update statement field on the channel.

[Former Member]

Javaguy,

I suppose- I have already provided the answer and close the thread. Pls read my answer in thread before posting the same information.

chirag

[Former Member]

Hi Chirag,

I hope i understand your problem, Can you show me your query according to that we will help you out.

Thank you.

[Former Member]

Hi,

Are you using BPM?

If yes, then fetching the data from Detail tabl is easier, you have to use Receiver 'Select' structure of JDBC.

-Supriya.

[Former Member]

I am not using BPM whithoug BPM i am looking at....

chirag