cancel
Showing results for 
Search instead for 
Did you mean: 

sender jdbc adapter....update query?

Former Member
0 Kudos

Dear Team,

we are selecting date from master database and client dont want to add any other field for update query.

using select option, how to avoid selecting duplication records.

can i use current date and time for selecting the records for every select statement?

if yes then how to do it and what we need to update in update query??

Regards,

Chinna

Accepted Solutions (1)

Accepted Solutions (1)

allamudi_loordh
Active Participant
0 Kudos

Hi Chinna,

Check like this below.

The following SQL statement selects the product name, and price for today from the "Products" table:


SELECT ProductName, Price, Now() AS PerDate

FROM Products;


Similarly,


UPDATE ProductName, Price, Now() AS PerDate

FROM Products;


Regards,

Loordh.

Former Member
0 Kudos

thanks for the update Loordh,

if we the adapter is polling for every 1hr then will it work,

regards,

chinna

allamudi_loordh
Active Participant
0 Kudos

Hi Chinna,

If adapter is polling for 1hr duplicate records will be selected. Check the requirement how they need it.

set up some time daily to pull out all the records based on date. use scheduling the channels operation.

or other wise please let me know complete requirement to suggest.

Regards,

Loordh.

Former Member
0 Kudos

Thanks for the update,

requirement is in initial state, we can select the records based on the SYSDATE and SYSTIME-1.

right?

allamudi_loordh
Active Participant
0 Kudos

Hi Chinna,

My suggestion is to keep one field "FLAG" at database level .so that it can be used for selection and update. or  other wise select or update with Date alone.

regards,

Loordh.

Former Member
0 Kudos

Thanks for the update Loordh.

Answers (1)

Answers (1)

Former Member
0 Kudos

Hi Chinna,

Please explain your requirement.

Thanks,

Shemim

Former Member
0 Kudos

Hi Shemim,

we need to select few data from one of the client master data base.

they want us to pick the data based on current date.

so if the JDBC adapter is scheduled for every one hour, how to select the data with out duplication.

Regards,

Chinna

Former Member
0 Kudos

HI Chinna,

Since the table you intend to read is having master data, how it will contain duplicate entries.

Please tell me whether the client wants to inform your system with the newly added master data entries in their database, so that yours and client system will have the same master data.

Hope the client database master table will be having a particular field that convey whether it is a new entry or already read entry. You will be updating this particular field when you read the corresponding record.

Polling interval needs to be decided thinking of the implication on transactions.

Please reply if this is not helping to solve your issue.

Thanks,

Shemim

Former Member
0 Kudos

Thanks for the reply Shemim,

as you told, they want to keep both oracle and SAP data base on the syn,

i dont have complete filed details i need to update in the SAP.

they raised question to update SAP database based on the current date.

so while selecting the data i want to use current date as my key word and update the SAP database.

but if the scenario run for every 2 hrs then will the scenario pick same same as it picked last 2hrs back.  if yes? then how to avoid it.

Regards,

chinna

Former Member
0 Kudos


As Shamim highlighted, there should be some field in DB as (it contains master data), which will be unique and you can leverage in update query after checking with DB team there.

Or I will suggest to check at SAP team end to avoid duplicate records coming from SAP PI.

--Divyesh

Former Member
0 Kudos

Hi Chinna,

There should be a particular field (say staus) in the Oracle Database table which will convey whether the record is already read one or NEW one. Along with the current date, use this field (status) also in your select query where condition which will help you to identify only newly added records.

In the update statement, you will be changing changing this field (status) so that next time it will not be considered.

With this approach, polling time can be made as much less as possible so that both the systems will be in sync and your other transactions will be able to use the master data(exactly available in Oracle DB).

Thanks,

Shemim