Getting warning in Update query

[Sridhar Sivakoti]

Hi,

I want to update the data in SQL Server table, for that I have taken a job design as Sequentialfile(dummy)-->Transformer-->ODBC stage.

In Sequential file I have taken some dummy data and passing it into transformer stage and finally I have taken the update query in ODBC stage. when I run this job records getting updated but I am getting below warning

STG_SVC_MRCHNT_HEADER_UPDATE_PROCESS..trnPassThru.updateTgtTable: DSD.BCIPut call to SQLExecute failed.
SQL statement:UPDATE
STG_SVC_MRCHNT_HEADER_INBOUND
SET INTF_STATUS = 'PROCESS'
WHERE INTF_STATUS IN ('IN_PROCESS')
0 Rows affected.

dummy = 1

Please let me know what might be the reason and how can I eliminate this warning.

Regards
Sridhar

[chulett]

The 'warning' is simply the fact that your update sql did not update any rows. You eliminate it by not sending updates for non-existent rows. Use normal ETL techniques - existing keys in a hashed file for instance - to pre-qualify your work, inserts versus updates.

[Sridhar Sivakoti]

The 'warning' is simply the fact that your update sql did not update any rows. You eliminate it by not sending updates for non-existent rows. Use normal ETL techniques - existing keys in a hashed file ...

Thanks for response.
This query is updating the records, the only problem is giving the warnings and I can not use the keys since this table does not have any key. Is there any way to eliminate the warnings.

Regards
Sridhar

[chulett]

I've told you how to stop them, there's no magic switch to turn the warnings off that I've ever heard of. And something is not getting updated or it wouldn't tell you '0 rows affected'.

Seems like you'll need to do something like select a count for your where condition first, then only update if the count is > 0.

[ray.wurlod]

Make sure that you send it a value (let's say 1) and include a constraint in the WHERE clause that ? = 1

That way, DataStage will have sent one or more rows that will properly trigger the SQL statement.