calculating variable max() from target table in tmap?

[PinkyCZ]

Hi,

I'm struggling to find a solution for a following mapping issue. Table A mapped to table B. both tables have key "code" that the tmap is trying to insert/update in the table B along sith some details (Description etc.. not important). But all values inserted to the table B must have a value caluclated for the additional field "Sequence" existing there. Thi sfield is not se as a key (Code is), but it has to be updated for all rows in table B, it is a simple sequential like number, incresing for each record added.

So basically for every record processed in tmap, I would need somehow to calculate the "max" value variable there is for "Sequence" in the target "B" table and make the insert with +1 for any new code. If code exists in B, it shall leave Sequence as is, only update additional fields.

I did try with the B table to serve as another tmap input, but that only populates max vale there is at the job start and I can't seem to find way for it to icrease for the records that shoudl insert to "B".

So basically this "Sequence" field is like another key.. yet database is not set this way so its value has to be determined in the processing job...

Any ideas about how this could be tackled guys??

[Anonymous]

Hello,

Could you please elaborate your case with an example with input and expected output values? Which will be helpful for us to understand your requirement very well.

Best regards

Sabrina

[PinkyCZ]

Hi Sabrina, appreciate your reply... I'll try to illustarte this a bit better.

the source "A" atable referred looks as follows - similar to a log, it keeps track of some entry events.

Processing this log to the taget summary "B" table, existing entries there should update with the other details from the log. This bit works fine. Existig entries there however, have the addiitonal value assigned which in fact is a bit like unique key as well - just not set as autoincrement key on teh table itself., this gets updated by the legacy ssyte processing the entries so far. Once an entry is nserted, this new Sequence value should be assigned, on update however, this stays (each code has its unique Sequence no. assigend at the time of creation only)

The task is for those new entries in the log "A" table to insert AND to calculate their Sequence number. (Basically for each insereted row it should be max(Sequence)+1.

Despite I can get the Max at the start as global variable, I struggle to find a way for it to update with every record processed in the tMap - basically the Max(Sequence) should be evalueated at every row so that any new insert gets the correct +1 value.

So desired state of things is that all entries get into the summary table with all having their latest details (timestamp etc) updated... and for those that are getting inserted as new (Paul, Rick, Matt and Amanda) these get their Sequnce calculated as 109,110,111 and 112 respectively.

😰