how to check if a row exists in target and update if it does

[vikind]

I have a Target table with the following fields

T_MEM
-----
ID number
M_SEQ varchar2
Name varchar2
..


The ID and Name come from the source but the M_SEQ (sequence key)
has to be populated from a sequence generator.

what i want to acheive here is if a row exists in the T_MEM any
matching ID coming from source i want to update the T_MEM table with
the Name and other fields coming from source.

If the no row exists for the ID coming from source then i have to
insert a record into the T_MEM table with a new row and a sequnce value for M_SEQ.

to achieve this i have used a connected lookup on target and I try to match the
ID's and return the all ID and M_SEQ port from the lookup to an expression transformation
and i connect name and other fields from the source to the Expression tranformation.

I tried to use a router after expression to check if the ID is giving a default value
say -999 if it does then i send those rows to an Update strategy for Insert and
if it does not return default value i use Update Strategy for Update and each of this is
connected to two instances of target T_MEM.


I am not sure how to fit in the sequnce logic for M_SEQ in this and is my approach right.

thanx

 

[anant13703]

See..for this problem all you need is a series of transformations...1. Dynamic Lookup 2. Router along with othertransformations like Seq Generator, etc.
First thing first, use dynamic lookup to find out whether the row exist in the target. If it does then u will get the value of "newlookuprow" port as 0...if it is a new row then u'll ge this value as 1 or more(non-zero). now u can use a router and make two groups there...Ist with newlookuprow as 0 and other as 1.
Remember...u are passing all the fields to dnamic lookup and then carrying forward those fields to router. So here u get all the values u need to update. Take all the fields needed from the group "0" to target transformation which need an "update" (override the target options accordingly at the sesison level). Take all the rows from group"1" to another target transformation. In this particular data flow, connect the "nextval" port of the seq gen "directly" to the M_SEQ port in the target transformation. So the SEQGEN will be used only when there are rows to insert. I hope i have made the matter clear. im in kinda hurry so i ahve not reviewd what i have typed . It should work.

All the best
Anant

 

[sln007]

This is a related issue.....

I am attempting the SAME type of workflow, BUT,
the NewLookupRow value is not getting set correctly.

That is, the lookup does not seem to be working correctly.

I run my workflow, with an empty lookup table...load complete (393 rows)

I immediately run the same workflow again, and I get another
batch of 393 rows inserted.

Why might not the lookup be not operating correctly?

This is really beginning to P... me off.

THanks.

 

[sln007]

Issue is fixed.
Dynamic Lookup works, but ALL fields used in lookup must be
fully populated (RPAD with spaces if char fields are not full).

Steve N.
State of Ohio, MIS