How to update a row of a table using BODS map operation transform?

[former_member542326]

Hello all,

I am trying to update a value in a table using map operation transform as shown in below screenshot, I am using a lookup function between source and target and I want to update the salary value(highlighted) but I am unable to do so.

[xiaoming_wu · Accepted solution]

Map_Operation should be used together with "table compare transform" where after "table compare" rows will be flag as insert, delete, update and you can use Map_Operation to update the target table according to your business requirements. For example, you only care about the delete data, then for other type, you can choose "ignore".

Map_Operation and "table compare" are used as "CDC", you can use "table compare" to compare if there is any change for the source and load to target.

In your case now, since there is no "table compare" used, so all the rows are flag as "normal", according to your settings here, there is no action in target.

Best regards

Helen

[former_member208402]

kaushik goli

WHat is the your update criteria? you want to update all records or you want to update only few records after lookup?

[former_member542326]

7.jpgAccording to my requirement, I have to perform INSERT,UPDATE,DELETE without using table comparison transform.Hence I have taken one dataflow for each operation as shown in the below pic:

I have to perform the above operations based on the column "last update date", hence I have written a script for picking the max(last update date) as shown in the below pic:

CASE 1:

I am inserting a value in the target table just by using lookup function :

• I am taking a where condition in query transform as "BODS_TEST_SOURCE4.LAST_UPDATE_DATE>$MAX_LAST_UPDATE_DATE"
• I have created a lookup function in the query transform where my lookup table is my target table and comparison table will be my source.
• I am comparing those tables based on the "EMP_ID", whenever there is a match in "EMP_ID" between source and target, the return value of lookup function will not be NULL but if there is no such "EMP_ID" in target, the return value of function will be default value(i.e '0' in my case).

In the next step I am using a case transform to filter my insert case

• In this case only insert will be performed according to the specified conditions

Similarly I am trying to update a record by specifying the below case :

Here wherever my flag value is not equal to '0' states that the "EMP_ID" is present in the table but this needs to be updated according to the source hence in the next step I am using a map operation transform:

But I am unable to update the record as mentioned in the main question.

[former_member387642]

Usually the MAP operation needs record flagged with normal, update, insert or delete. so unless you use a table comparison the system cannot identify whether it is a update record or normal record.

In your case, you used the Query transform as the source for your Map Operation, query transform output records have the normal flag since all data are flagged as normal and you are discarding the normal flagged data the changes are not reflected in your target table.

You have to use the table comparison to find and flag the record types.

Regards,

Santhana.

[former_member542326]

I want to update only few records, for example,in the above case salary of "HARISH" is updated in the source but it is not getting reflected in the target.