How to update the restricted rows that were selcted using fetch

[Former Member]

Hi

I have to fetch one row at a time from my staging table using JDBC Sender Adapter, which is configured against AS400 DB2 and push it to R/3.

The select SQL looks like this in JDBC Sender Adapter

<b>select jedata, oxruns from fxjrnep2 where oxsts=' ' fetch first 1 rows only</b>

and

update sql is

<b>update suplib.FXJRNEP2 set OXSTS='SUC' where OXSTS=' ' </b>

The problem here is my select gets one row, which is what i want, but my update actually updates all the rows that satisfied the where condition oxsts=' ' instead of one.

I couldn't use <b>fetch first 1 rows only</b> at the end of the update sql to update the selected row. DB2 does not allow fetch in update sql.

Can anyone suggest solution here or recommend alternative?

Best Regards

Venkat

[former_member431549]

We use a stored procedure to select rows and update the status. We could never get as/400 to work foolproof on the sender cc.

[prabhu_s2]

check for update query where u can club the select stmt u had used with udpate like:

update suplib.FXJRNEP2 set OXSTS='SUC' where OXSTS = (select jedata, oxruns from fxjrnep2 where oxsts=' ' fetch first 1 rows only

)

just need to try combinations of this type.

if still this is not working use a bpm and get all the records where OXSTS is null and process record one by one within a block

[Former Member]

I have tried your select in update - got an error. What my Db2 experts says here is FETCH not allowed in sub selects. DB2 is smart in figuring whether its direct select or sub select.

If i try through BPM approach how would I update the data base after it is processed?