DSXchange

Posted: **Fri Dec 01, 2006 8:23 am**

When updating a DB2 file, is there a way to accumulate a count for a field in place of updating the count.

i.e. Key 1 Count = 2

Update

Key 1 Count = 5

Updated record

Key 1 Count = 7

Is there a quick way to do this without reading the DB2 file, doing a lookup, if it exist, calculate the new value and update the record or can I do it all in one step. Thanks

Posted: **Fri Dec 01, 2006 8:31 am**

DB2 'file'?

Are you asking if you can update a field in a table without knowing the original value of the field? Do you mean something like this using standard SQL:

Code: Select all

update tablename
set Count = Count + 5
where Key = 1

Posted: **Fri Dec 01, 2006 9:21 am**

Yes. When entering the DB2 File Stage, we don't know what the original count is. Normally, update will replace the original value and not accumulate it. Can I manually code the update where Update ....Where... Set Count = Count + Previous_stage.count

Posted: **Fri Dec 01, 2006 2:54 pm**

You've answered your own question.

If you don't know what the original count is, and need to know, then the only way is to interrogate the table.

You could do this in a previous job with a query such as

Code: Select all

SELECT KEY,COUNT(*) FROM DB2TABLE GROUP BY KEY;

DSXchange

Update DB2 file

Update DB2 file