JDBC Sender: SELECT with ORDER BY and rownum

[Former Member]

Hi,

in our DB we have more than rownum (500) entries. Now, the SELECT statement of the JDBC sender adapter should read the first 500 entries of the table with the lowest entries in field PKEY. This field contains an increasing number:

select * from table where status = ' ' and rownum <= 500 order by pkey.

The correct data is read and transferred to the Integration Server.

But what UPDATE statement should we use to flag the read entries? There exists no "ORDEY BY" clause. If we use only "update table set status = 'X' where status = ' ' and rownum <= 500" the wrong db-lines are updated.

If it is possible, we don't want to use stored procedures. Any help?

Regards,

Daniel

[Former Member]

Hi Daniel,

Try this thing. It should work.

UPDATE table SET status = 'X' where pkey IN (select PKEY from table where status = ' ' and rownum <= 500 order by pkey)

Let me know in case of any issues.

Thanks

Amit

[VijayKonam]

Daniel,

Going with a nested query would help here. Ude the query something like -

update TableName set status = where where pkey in (select PKEY from table where status = ' ' and rownum <= 500 order by pkey)

VJ