on 2018 Oct 11 6:58 AM
I have one data flow as below source table -----> query transform ------> table comparison ----> target table
The columns present in the source table, with below data:
outlet_id, outlet_manager, outlet_city
===================================
M102345, Alan SeanYY, AustinXX
M102346, Jan Scott, Princeton
M102347, Santiago Oxford, Orlando
the columns present in the target table, with below data:
outlet_id, outlet_manager, outlet_city, created_date, last_updated_date ==================================================================MM102345, Alan Sean, Austin, 01/10/2018, 02/10/2018
M102346, Jan Scott, Princeton, 03/10/2018, 04/10/2018
Table comparison transform is set as
a) "Input primary key columns" = outlet_id
b) "compare columns" = outlet_manager, AND outlet_city
Question:
1) Since outlet_id M102347 is not existed in target_table, it will insert into target_table.
What can I do, so that created_date column is assigned with current date time as well when this data row is inserted in targe_table.
2) Since outlet_id M102345 is existed in target_table, it will update column outlet_manager and outlet_city in target_table.
What can I do, so that last_updated_date column is assigned with current date time, and created_date remained (not changed) when this data row is updated.
Regards,
Rock
Thanks.
Request clarification before answering.
Yes, if there are no timestamps in your input, you can pick up created_date from the target table before the table_comparison with a lookup or a left outer join. And assign sysdate() when null.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
There is alternative way to resolve this problem, based on hint provided by Dirk in https://answers.sap.com/questions/680797/bods-how-to-update-some-columns-only-in-table.html.
Steps to implement this alternative way:
1) Add additional Map_Operation transform between Table_Comparison and Target_Table.
2) Go to tab "Mapping.
3) Click on column that should not be updated, then enter mapping expression "before_image(your_column_name)" in TextBox "Update Mapping".
4) Repeat step 4, for other columns that should not be updated.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hey Dirk Venken...thanks for youradvice.
If created_date and last_updated_date column are added into source table inside Table_Comparison transform, and assigned these 2 columns with current datetime value, then it will update column outlet_manager, outlet_city, last_updated_date as well as column created_date in target_table (e.g. outlet_id M102345).
Column created_date should not be updated. Can it be done?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Make sure your input data stream has exactly the same structure before the Table_Comparison transform: you must add created_date and last_updated_date and assign them the current datetime value.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
81 | |
30 | |
10 | |
8 | |
7 | |
7 | |
6 | |
6 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.