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
Getting warning in Update query
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 35
- Joined: Tue Feb 13, 2007 5:30 am
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 35
- Joined: Tue Feb 13, 2007 5:30 am
Hi chulett,
Thanks for response.chulett wrote: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 ...
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
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
That way, DataStage will have sent one or more rows that will properly trigger the SQL statement.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.