Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
My requirement is to load data from excel to oracle table with below conditions
Table columns are
cust_no,Name,Item,Orders,Location,Flag
cust_001, John, Watch,3, Mumbai, I-------------------->flag should change
1.If record already exists in table then FLAG column should change from "I" to "U"
Expected output:
cust_no,Name,Item,Orders,Location,Flag
cust_001, John, Watch,3, Mumbai, U---------------------->Already existing record on table
cust_002, Mathew, Bag, 3, Bangalore, I
cust_001, John, Watch,3, Mumbai, I-------------------->record from excel should insert like this
My job design looks like below screenshot
The problem is new records from excel inserting with flag "I" but existing record flag is not Insert as the new record it just update the existing record Flag
1.InsertRec--->output-->action on table --->Insert
2.Update -->output-->action on table -->Tried with Insert or update ,update or insert,update but it's not inserting as new record
When I tried with Insert as action on table it is inserting the record which is already in table is as below
cust_no,Name,Item,Orders,Location,Flag
cust_001, John, Watch,3, Mumbai, U
when I work with start_date,end_date using SCD component it's giving me exactly what I'm looking for.
Hope you understand the problem.
As soon as possible please help me to resolve this!
Thanks in advance
Hi Sara,
How are setted the "Catch lookup inner join reject" option on both output flows?
It should be "True" for Insertrec flow, and "False" to Update flow.
Also, you can hard code the FLAG value for both flows, "I" for Insertrec and "U" for Update.
Hi @TRF,
Like you said already I kept the same, please have a look at the below screenshot
am I doing any mistake in the variables "updateFlag" and "InsertFlag"?
Hi
If I understand your request well, you want to insert the source record also into db even though it already exists. There is a design issue in your job, the existing records always go to the update output on tMap and are updated in db.
Regards
Shong
Hi @TRF
I just replaced variable and hard code as "i" and "U"
and I got below output
I kept Action on table for both output is as "INSERT" only
How is the "Catch lookup inner join" option for Insertrec flow?
Regarding the variable "InsertFlag", as soon as the record is not found in db because it is a new one, you should simply hard code the value ("I") in the output flow like this:
Also think you can do the same for Update flow with the value "U", don't you?
Hi @TRF,
Catch lookup inner join reject for "Insertrec" is true only
Hi,
I hope I got output like I shown the above screenshot.
I just want to make sure whether it works fine by adding date column.
Something like
start_date and End_date
Will work on it and get back to you if I face any problem
I've just read again you original post, so I understand every record from Excel file must be inserted into the db with a value of "I" if it doesn't exists yet in the db, and a value of "U" if it exists.
IMHO, "Action on data" should always be set to "Insert" and you just need 1 output flow for both new and exisiting records.
For this, use an "Left Outer Join" in tMap, then decide the value of the flag field depending on the join result (field will be null for new records, so you just have to replace the value by "I" in this case):
I've not tested but should works.