SELECT and UPDATE only one record for each poll interval

[former_member460664]

Dear All,

I have a issue that is related to update statement in sender jdbc adapter. My requirement is, for example I have 10 new records in table with Status=N. I have to pick only one record for each poll interval specified in communication channel. By using select statement I have fetched first record and send it to SAP. But update statement has updated all the 10 records at one time with Status=O. From next poll interval onwards, there are no records with Status=N. But I should update only one record that too is fetched by select statement. How to do this? Please let me know the solution.

Regards,

Ashok.

[baskar_ramasamy · Accepted solution]

Hi Ashok,

SELECT TOP 1 * from tablename WHERE status = N

update tablename set status=O where status =N (select Top 1 from tablename where status=N)

Try this, it will pick 1 record from the table for each polling.

[former_member460664 · Accepted solution]

Thank you Bhaskar.

I have written SELECT TOP 1....... query in query statement. It has fetched the single record from all 10 records. But we are facing problem with UPDATE statement. Update statement updates all the 10 records. From next poll interval, I have no new records to fetch.Instead of updating all records, I want to update only one record that is fetched by SELECT statement. Is this possible??

Regards,

Ashok.

[baskar_ramasamy · Accepted solution]

Ashok,

Then please add a new field rowid in the table and use the below select & update statements.(this is for fetcing 1 record and updating that record status=O)

SELECT TOP 1 * from tablename WHERE status = N ORDER BY rowid ASC

UPDATE tablename SET status = O WHERE status = N AND rowid = (SELECT TOP 1 rowid from tablename WHERE status = N ORDER BY rowid ASC)

Thanks,

Baskar

[former_member201264]

Hi Ashok,

You could do as below:

Create your sender data type row header with occuurance 1-1.

Now it will process only once record and send to SAP.

Create Sender JDBC Channel with select statement as per your Where clause.but put Upadte as <TEST> .

Make sure your poll interval is very less time to process one by one.

Then get the response from SAP and map to your Receiver DT type with the same table with one Key field to update the same record.

Regards,

Sreeni.

[former_member460664]

Dear Sreeni,

If I use <TEST> in UPDATE statement of sender channel....  records have been picked for multiple times. I should update the record that is fetched by SELECT statement.

Regards,

Ashok.

[nabendu_sen]

Hi Ashok,

Database team needs to come up with design and Select query to achieve the above requirement. They may introduce a new column to uniquely identify records when you are attempting to fetch the records with SELECT statements when 1st update is already done.

[rajasekhar_reddy14]

Hi Ashok,

This is bit tricky, you need to write update statement with more logic, but still not sure it will updates only one record.

check with data base expert to frame a query like sorting all records and pull one record and while updating also need some logic.

Regards,

Raj

[baskar_gopalakrishnan2]

You can add a column name something like flag in the table and initially set to FALSE. After update change it to TRUE. Something similar below..  Ofcourse you need to explicitly make a query with some primary key to refer every single record along with flag column. This might help.

Select Query:

SELECT column FROM TABLENAME WHERE FLAG = "FALSE"

Update Query:

UPDATE TABLENAME SET FLAG = "TRUE" WHERE FLAG = "FALSE"