Hi can anyone help me with this !

[onebyone]

Hi can anyone help me with this !

My Source and Target tables are same.

Source

CREATE TABLE JUL_s_EMP
(
EMPNO number(4) NOT NULL,
ENAME varchar2(10),
JOB varchar2(9),
MGR number(4),
HIREDATE date,
SAL number(7,2),
COMM number(7,2),
DEPTNO number(2)
);

Target

CREATE TABLE JUL_t_EMP
(
EMPNO number(4) NOT NULL,
ENAME varchar2(10),
JOB varchar2(9),
MGR number(4),
HIREDATE date,
SAL number(7,2),
COMM number(7,2),
DEPTNO number(2)
);

I have done a simple pass thru mapping

Now when i make a small update in source table say salary of empno 7377
has been updated from 12000 to 13000.I want that change to be reflected in the
target table without inserting another row.What i mean to say is it should just

update the sal column in the target table with the new value.

How do i achieve this.

what options should i set in TARGET TABLE OPTIONS under SESSION PROPERTIES.
Which ones should i check.

Insert
Update(as Update)
Update(as Insert)
Update(else Insert)
Delete
Truncate Table

Can i achieve this change using this simple pass thru mapping or do i need
to use an Update Strategy Transformtion/Lookup transformation.

Please advise. I am a newbie.

Regards,
SAnjay

 

[vikjain]

Hi Sanjay,

did you try Update( as insert).

 

[dinzana]

1. In your mapping, create a lookup to the target table on empno.

2. Create an update strategy that has a condition something like IF(empno is NOT NULL, DD_UPDATE, DD_INSERT), which, in a nutshell, says....

"If the empno coming from the source exists in the target table, then update the row, if the empno coming from the source does not exist in the target, them insert the row."

I hope that this helps.

-dinzana

 

[sandhu]

Hi,

Check the 'Update as update' option.
I think you might have to specify a primary key also, in your target table for this transformation to work.
'Update as update' will only update an existing row and will not insert another row in the target table.
Also, read 'Setting the Update Strategy for a Session' page in the Informatica help.
Hope it helps.

 

[vsn2009]

hi,
I have a problem with look up.
I need to have a field value from another target table say"DD".
so i created a look up table in my current map.but how to link its input port??
can anyone give a good example for unconnected look up.
thanks!!!

 

[bardhaan]

Hi,
To get a field from another table,you need to have a primary key or primary key combination in your target table and also need to use that in lookup condition in the lookup transformation.
Otherwise there can be multiple value in the lookup table for a single input from the source table, which will result in improper loading.

Regards,
Ani.

 

[Casemore]

For updates: ensure the target has a primary key to update on, set 'treat source rows' as update, select 'update as update' in target options.

If you want to update else insert if it doesn't exist: ensure the target has PK, set 'treat source rows' as insert, select 'insert' and 'update else insert' within the target options.

As for lookups, if you do have multiple rows in your lookup Informatica will select the first or last row depending on option selected. But as Bardhaan said this will be improper loading - not a true join as such.