Page 1 of 1
Insert and update
Posted: Tue Jun 20, 2006 8:14 am
by rafidwh
Hi All,
I need to update or insert new rows based on key columns.
I need to do some calculation on the updated records before populating into the table.So I am taking two links from the transformer one for insert and the other for update and oci as my lookup.
I gave the condition in the constraint like IsNull(keycolumn) for insert and NotIsnull for update.
The update actions choosen from the oci are insert new rows and update existing rows respectively
But my concern is even when I am trying to populate new record it is going through the update link.
Any suggestion Please
Regards.
Saik
Re: Insert and update
Posted: Tue Jun 20, 2006 8:35 am
by kris007
rafidwh wrote:
I gave the condition in the constraint like IsNull(keycolumn) for insert and NotIsnull for update.
Is the
keycolumn from the input link or the reference link. I usually differentiate between updates and inserts using the LinkVariables with the following condition in the Constraint
Code: Select all
Updates--> InputLink.NOTFOUND <> @TRUE
Inserts--> InputLink.NOTFOUND = @TRUE
I would also suggest you use hashed file as your lookup instead of OCI stage for performance benefits.
HTH
Re: Insert and update
Posted: Tue Jun 20, 2006 9:39 am
by DSguru2B
rafidwh wrote:
I gave the condition in the constraint like IsNull(keycolumn) for insert and NotIsnull for update.
The keycolumn must be from the reference link and not from the source or else it will always go down the path of Update.
Posted: Tue Jun 20, 2006 2:46 pm
by ray.wurlod
Do you have Not(IsNull(keycolumn)) as (part of) the constraint on the update link?
Posted: Tue Jun 20, 2006 11:39 pm
by Alagusakthivelan
Hi
Updates--> InputLink.NOTFOUND <> @TRUE
Inserts--> InputLink.NOTFOUND = @TRUE
this will not allow in OCI reference stage, even though it shows it wont take the correct records. use IsNull for OCI stage reference.
check the constraint value in stage variable and use the stage variable in constraint.
Posted: Wed Jun 21, 2006 1:26 am
by rafidwh
yes Ray
I defined the key colunm in constraint for update as
Not(IsNull(srckeycolumn))
Posted: Wed Jun 21, 2006 1:27 am
by rafidwh
And I have 8 key coluns based on that I need to update or insert
Posted: Wed Jun 21, 2006 2:51 am
by loveojha2
rafidwh wrote:yes Ray
I defined the key colunm in constraint for update as
Not(IsNull(srckeycolumn))
Change it to something like
Not(IsNull(lkpkeycolumn))
and your problem should get resolved.
Posted: Wed Jun 21, 2006 4:26 am
by kumar_s
rafidwh wrote:And I have 8 key coluns based on that I need to update or insert
In that case InputLink.NOTFOUND should be your simple option, instead of give 8 different condition.
Posted: Wed Jun 21, 2006 5:49 am
by sb_akarmarkar
You need to give it condition for insert like InputLink1.NOTFOUND or InputLink2.NOTFOUND and ..... Upto 8 links and for Update NOT(InputLink1.NOTFOUND and InputLink2.NOTFOUND and ... Upto 8 )
above should be fine..
Thanks,
Anupam
Posted: Wed Jun 21, 2006 8:11 am
by kumar_s
sb_akarmarkar wrote:You need to give it condition for insert like InputLink1.NOTFOUND or InputLink2.NOTFOUND and ..... Upto 8 links and for Update NOT(InputLink1.NOTFOUND and InputLink2.NOTFOUND and ... Upto 8 )
above should be fine..
Thanks,
Anupam
Anupam - It is 8 Keys and not 8 links.