Insert and update

[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

[kris007]

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

[DSguru2B]

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.

[ray.wurlod]

Do you have Not(IsNull(keycolumn)) as (part of) the constraint on the update link?

[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.

[rafidwh]

yes Ray
I defined the key colunm in constraint for update as

Not(IsNull(srckeycolumn))

[rafidwh]

And I have 8 key coluns based on that I need to update or insert

[loveojha2]

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.

[kumar_s]

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.

[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

[kumar_s]

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.