Comparing data from 2 tables

[Former Member]

Hi All,

This is a JDBC-Prooxy scenario. I used a JDBC sender adapter to pick records from the table and used a proxy to to pass the data to R/3 and called a fuction module in R/3. The scenario worked fine as expected.

Now the problem is there is another table in the database which contains the history. Now I need to compare the records in 2 tables and pass the data to R/3 if a record does not exist in second table(This can be done by a select querry), but if a record exists I need to check for "amount" field again. If the amount in history file is lesser, the record has to be passed with the amount(difference between the first table and history table). If the amount is also same, I should not send the record to R/3.

How can I achieve this? Where can I do these comparisons and pass the data to R/3?

Thanks in Advance.

Regards,

Jai Shankar.

[bhavesh_kantilal · Accepted solution]

Jai,

The solution would like this,

1. Receive -- To select data from Table 1

2. Send Synch -- To select data from table 2 ( refer my blog )

3. Transforamtion ( n:1 ) -- combine the data from step 1 and step 2 and do the mappinng . Set some field to determine if the IDOC should be sent or not.

4. Switch -- Check for the data and then send the IDOC if needed else terminate the BPM anbd process flow.

Regards,

Bhavesh

[bhavesh_kantilal]

Jai,

You need a BPM and you need to select the data from the DB for the history table and then do a mapping and determine whether IDOC is to be sent out or not.

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

Regards,

Bhavesh

[Former Member]

Bhavesh,

Thanks a lot. I am very new to BPM. Can you pls tell me how my BPM process must look like? The steps involved in the BPM...

Thanks & Regards,

Jai Shankar.

[bhavesh_kantilal]

Jai,

I have just listen them above!!!

The only change would be in step 3~~ how you segregate the valid and invalid data and how you are going to use a Switch Variable. This depends on exact mapping requirements.

Regards,

Bhavesh