DSXchange

Hi Everyone,

I am trying to update a DB2 table using user defined SQL. The reason I am using an user defined SQL is that , I need to Update a Field to a New value from its Old value, and in the "Where clause" i need the Old value to pick the proper row.

Here are the fields from the table that I am using:

VALUE_KEY, SERVICE_ID, DIM_KEY, CLIENT_ID, WEEK_KEY.

I need to update VALUE_KEY and SERVICE_ID to new values depending on the existing values of DIM_KEY, CLIENT_ID, WEEK_KEY and VALUE_KEY

So the update sql will be something like ,

UPDATE ATTRIBUTE_TABLE SET VALUE_KEY=?,SERVICE_ID=?
WHERE DIM_KEY=? and CLIENT_ID=? and WEEK_KEY=? and VALUE_KEY=OLD_VALUE_KEY.

I have the OLD_VALUE_KEY(the Old value ,which I need to update to New value) field in the columns list.

Now how can I pass this Old value to the "Where" clause of the query in the DB2 API stage?

When I set the user defined SQL as

UPDATE ATTRIBUTE_TABLE SET VALUE_KEY=VALUE_KEY,SERVICE_ID=SERVICE_ID
WHERE DIM_KEY=DIM_KEY and CLIENT_ID=CLIENT_ID and WEEK_KEY=WEEK_KEY and VALUE_KEY=OLD_VALUE_KEY

it is not updating the value. Can we use the fields like VALUE_KEY=VALUE_KEY in User defined SQL Or we need to handle this in another way?

If someone can help me on this I will be really thankful.

Thanks,
Arnie

UPDATE ATTRIBUTE_TABLE SET VALUE_KEY=?,SERVICE_ID=?
WHERE DIM_KEY=? and CLIENT_ID=? and WEEK_KEY=? and VALUE_KEY=?

Where you bind the OLD_VALUE_KEY column into the proper place in the SQL just like every other field.

Hi Craig,
Thanks for quick reply.
But, I am not able to see your complete post as the privilege is for only premium members
Could you please make your post public for some time?

Thanks,
Arnie

Didn't notice this was your first post here and you can't be missing all that much of what I posted, so sure.

Hi Craig,
Thank you very much.
It worked..


Regards,
Arnie