JDBC to IDOC Scenario - select data in jdbc based on multiple conditions

[Former Member]

Hello

I have a JDBC to IDOC Scenario. I have to select the records in JDBC based on different conditions each time. For example I have to select based on company code '1000' and Employee claasification 'E1' and date range. After I post these records in SAP again I want to select other records for some other company code '2000' and different business area and different dates. Basically I want to extract data multiple times based on different conditions.

Hiow do I achieve this?

Another question is in the JDBC to IDOC scenario since the sender adapter is JDBC and the sender adapter polls depending on the duration of time ( say 60 secs ) in the adapter once after I extract the data based on a condition how do I control in such a way that the same data is not extracted again.

Thanks

Naga

[Former Member]

Hi Naga,

There should a column(flag) in that table which tells the status of the record..

flag=Y, the record is posted.

flag=N, the record is not posted.

I want to extract data multiple times based on different conditions

This you can acheive using the following query:

select * from table_name where flag='N'

how do I control in such a way that the same data is not extracted again.

This is done by:

update table_name set flag='y' where flag='N'

The update statement will update only those record which has been 'select'ed for that interval. Hence preventing those records getting selected for the second time.

Hope this helps..

Ragards,

Vishal

Edited by: Vishal.P.Kulkarni on Nov 6, 2009 10:49 AM

Edited by: Vishal.P.Kulkarni on Nov 6, 2009 10:50 AM

[Former Member]

I have to select the records in JDBC based on different conditions each time

Might not be possible with current JDBC adapter configurations available as we cannot invoke dynamic SQL queries.

Even if you go for a stored procedure approach, the stored procedure should contain excatly one SELECT statement.

condition how do I control in such a way that the same data is not extracted again.

we have Update SQL Statement option available in sender JDBC configuration where you can update a status flag.

So the select query which you use for fetching records should be based on status field value.

[Former Member]

Hi Naga,

 I have to select the records in JDBC based on different conditions each time. For example I have to select based on company code '1000' and Employee claasification 'E1' and date range. After I post these records in SAP again I want to select other records for some other company code '2000' and different business area and different dates. Basically I want to extract data multiple times based on different conditions. 

-->

Such requirements cant be handle through select query of the sender...but you can handle this in the message mapping area.....you can fire a select query in the database to pick up records in a batch of 10K (do not keep any condition on this except for sorting). After the records come into PI you can send the message to your target based on the unique combination of "Company code+ Employee clasification + date range" handling this in the message mapping.

Another question is in the JDBC to IDOC scenario since the sender adapter is JDBC and the sender adapter polls depending on the duration of time ( say 60 secs ) in the adapter once after I extract the data based on a condition how do I control in such a way that the same data is not extracted again.

You can use the N--> C logic

The data records that you pick have a corresponding control table i assume. There should be a field STATUS where the initial status of record should be N.

After you pick the records this status should be made C so that only those records present in the database with status = N are picked up.

Mention the condition Status = N in the select query.

Thanks

Dhwani

[Former Member]

Naga,

Basically I want to extract data multiple times based on different conditions. 

You might be interested to use Stored Procedure. I haven't tried this, but I guess this is do-able. You can give it a try and share with the community

how do I control in such a way that the same data is not extracted again.

Yes, this can be achieved.

1. Use Select-Update and flag the selected rows by Update statement.

2. Use Isolation Level = Serializable in Sender CC.

Hope this helps.

Regards,

Neetesh

Edited by: Neetesh Raj on Nov 5, 2009 9:40 PM