Update Strategy 3

[cognos11]

I have a look up transformation and update strategy transofrmation.

I am trying to update the row in the target table if the row exists otherswise insert the row.

When i run the workflow, my target table successfully updates rows( which have matching source id's).However for the inserts( ie for the new rows in source ) it does the following.

1) Everytime i run the workflow,it inserts the amount but leaves the ID column blank.

eg

Before Worklfow

Source

ID Amt
1 100
2 100

Target

ID(PK) Amt
1 200

After Worklow is run

Target

ID(PK) Amt
1 100
100
2) When i run the worklow again,it inserts the same row or rows again,with id columns Null.

Target

ID Amt
1 100
100
100

In other words,My updates are happening correctly but Inserts are not.

I used Data Driven option,Checked the INSERT and Update else Insert boxes in session configuration.

My update Strategy expression looks like this
IIF(ISNULL(Target_ID),DD_INSERT,DD_UPDATE)


Do i need to have a seq generator transformation after Update to populate target Id columns?

 

[cognos11]

this is an update to the my earlier question.
I have included session log in this.
As you can see my my updates are happening correctly but the informatica is not allowing new inserts into the target table.It says cant insert Nulls in the ID column.

I have assigned primary key to target id column.
Is the error because of this.?

please advise

 

[papagari]

Hi, The only reason I could see for this error is:

You might have mapped source id from Lookup port (this happend to be your target field) and not the one coming from source. So whenever your target source id is null, then you need to insert a row. It is inserting other data but source id. This is just because you wrongly mapped source id to Update strategy and then to target from lookup port and not from source table port. Check it out.

 

[cognos11]

Thanks papagari for your reply.But i couldnt understand what you written.I guess i gave you little info.Please reply

This is how my mapping looks like.



Src Qua Lkp Tra
------- --------
src_id -------------> src_id(I,O)
src_amt amt(O,L) from tgt tbl
Lkp_ID(O,L) from tgt tbl

Lookup condition: Lkp_id = src_id

I am matching lkp_id in lkp trans( which originates from Targtet tbl) with src_id from src qualifier.


Lkp Tra Up Str
-------- ------
src_id(I,O)
amt(O,L) from tgt tbl ---------> amt(I,O)
Lkp_ID(O,L) from tgt tbl ---------> Lkp_Id(I,O)

Upd Str Expression:iif(isnull(Lkp_ID),dd_insert,dd_update)


Up Str tgt
------ -----
amt(I,O)---------> amt(I)
Lkp_Id(I,O)------> Id(I,PK)





THIS IS THE SESSION LOG


WRITER_1_1_1> WRT_8124 Target Table TGT_TEST :SQL INSERT statement:
INSERT INTO TGT_TEST(ID,AMOUNT) VALUES ( ?, ?)
WRITER_1_1_1> WRT_8124 Target Table TGT_TEST :SQL UPDATE statement:
UPDATE TGT_TEST SET AMOUNT = ? WHERE ID = ?
WRITER_1_1_1> WRT_8124 Target Table TGT_TEST :SQL DELETE statement:
DELETE FROM TGT_TEST WHERE ID = ?
WRITER_1_1_1> WRT_8270 Target connection group #1 consists of target(s) [TGT_TEST]
WRITER_1_1_1> WRT_8003 Writer initialization complete.
WRITER_1_1_1> WRT_8005 Writer run started.
WRITER_1_1_1> WRT_8158



TRANSF_1_1_1_1> DBG_21216 Finished transformations for Source Qualifier [SQ_SRC_TEST]. Total errors [0]
WRITER_1_1_1> WRT_8167 Start loading table [TGT_TEST] at: Mon Jan 19 15:33:11 2004
WRITER_1_1_1> Mon Jan 19 15:33:11 2004
WRITER_1_1_1> WRT_8229 Database errors occurred:
ORA-01400: cannot insert NULL into ("PWRMETA"."TGT_TEST"."ID&quot

Database driver error...
Function Name : Execute
SQL Stmt : INSERT INTO TGT_TEST(ID,AMOUNT) VALUES ( ?, ?)
Database driver error...
Function Name : Execute Multiple
SQL Stmt : INSERT INTO TGT_TEST(ID,AMOUNT) VALUES ( ?, ?)
WRITER_1_1_1> Mon Jan 19 15:33:11 2004
WRITER_1_1_1> WRT_8114
Row # [1] in bad file

WRITER_1_1_1> CMN_1053 : Rowdata: ( RowType=0 Src Rowid=3 Targ Rowid=3
ID (IDouble: "(NULL)"
AMOUNT (AMOUNTouble: "(NULL)"
)

WRITER_1_1_1> Mon Jan 19 15:33:11 2004
WRITER_1_1_1> WRT_8229 Database errors occurred:
ORA-01400: cannot insert NULL into ("PWRMETA"."TGT_TEST"."ID&quot

Database driver error...
Function Name : Execute
SQL Stmt : INSERT INTO TGT_TEST(ID,AMOUNT) VALUES ( ?, ?)
Database driver error...
Function Name : Execute Multiple
SQL Stmt : INSERT INTO TGT_TEST(ID,AMOUNT) VALUES ( ?, ?)
WRITER_1_1_1> Mon Jan 19 15:33:11 2004
WRITER_1_1_1> WRT_8114
Row # [2] in bad file

WRITER_1_1_1> CMN_1053 : Rowdata: ( RowType=0 Src Rowid=5 Targ Rowid=5
ID (IDouble: "(NULL)"
AMOUNT (AMOUNTouble: "(NULL)"
)

WRITER_1_1_1> WRT_8168 End loading table [TGT_TEST] at: Mon Jan 19 15:33:11 2004
WRITER_1_1_1> WRT_8141


I hope this is enough info for you to understand my mapping.

 

[anant123]

Hi,
What papagari meant is that you must be connecting the 'ID' field from lookup transformation to the update transformation. If so then DONT DO THAT.
You must be having some Expression Transformation before Lookup Transformation which is passing the 'ID' field to LOOKUP. Use this Field (from Expression transformation) to connect to Update Transformation; and then try running the session.

Problem may be occuring due to following scenario.
Consider the ID value 123 is going into LOOKUP transformation. This 123 is not there in the Target table. So after the comparision, it will return NULL in the ID field of LOOKUP. You must be passing this field to the Update Strategy transformation. Now You are opting for Insert when the ID is null...which is exactly what it is doing.

cheers
Anant

 

[papagari]

Hi Cognos,

Simple do this

Up Str tgt
------ -----
amt(I,O)---------> amt(I)
Lkp_Id(I,O)------> Id(I,PK)

in the above in place of Lkp_ID, put src_id.

Your Up Str contains 3 ports.

amt
Lkp_Id
Src_Id

Now Map like this

Up Str tgt
------ -----
amt(I,O)---------> amt(I)
Sr_Id(I,O)------> Id(I,PK).

I think it is clear. If not let me know.

 

[cognos11]

In place of Lkp_id put src_id.what does this mean.Do i drag the src_id from lookup and then link it to the target?
If so, what about the lkp_id in Update strategy?
Should i just drag the lkp_id from lkp trans and then to the update trans.?


It is still not clear.

Could you just show the flow from lkp to update to target with respective ports as i have shown above.It would be easier to understand the mapping that way.

Pls respond

 

[cognos11]

papagiri

What exactly happens in the lookup.I know the lookup port(here lkp_id) is matched with the source id and all those rows that meet the condition are passed through the lookup port.Right?

What about the other lookup ports in the transformation which are not part of the lkp condition.?

For instance source amount which dont go through any trnasormation.Arent they affected by lookup condition?

 

[bardhaan]

Hi Cognos,
This is your plan, what you have given, for updating the target table.
Lkp Tra Up Str
-------- ------
src_id(I,O)
amt(O,L) from tgt tbl ---------> amt(I,O)
Lkp_ID(O,L) from tgt tbl ---------> Lkp_Id(I,O)

Upd Str Expression:iif(isnull(Lkp_ID),dd_insert,dd_update)


Up Str tgt
------ -----
amt(I,O)---------> amt(I)
Lkp_Id(I,O)------> Id(I,PK)

Just do a bit modification to what you have done.That will solve your problem.

Lkp Tra Up Str
-------- ------
src_id(I,O) ----------> src_id(I,O)
amt(O,L) from tgt tbl ---------> amt(I,O)
Lkp_ID(O,L) from tgt tbl ---------> Lkp_Id(I,O)

Upd Str Expression:iif(isnull(Lkp_ID),dd_insert,dd_update)


Up Str tgt
------ -----
amt(I,O)---------> amt(I)
src_id(I,O)------> Id(I,PK)

I'm sure this will solve your problem.
Regards,
Ani

 

[cognos11]

Thanks Everyone Guys

I finally understood how lookup transformation works.

Thanks a lot for your patience in answering my questions.

You all deserve a start.