One Star

[resolved] If Condition Then Update Else Insert

Hi there,
how can i create a job that makes up this logic:
if condition
then
update
else
insert
condition is a check on a query result (if there is a record)
the query is something like (SELECT id FROM table WHERE date LIKE '....')
My Input is a tSugarCRMInput
My Output are two tSugarCRMOutput
I'm using Talend 5.3.1
10 REPLIES
Community Manager

Re: [resolved] If Condition Then Update Else Insert

Hi
There is a global variable like ((Integer)globalMap.get("tSugarCRMInput_1_NB_LINE")) that counts the numbe of the processed data, you can check if there is at least one record with this global variable, and then use runIf connector to fires different processing. For example:
tSugarCRMInput--main--tJava_1
|
onsubjobok
|
tJava_2--runIf_1--->....tSugarCRMOutput_1( for inserting)
runIf_2 --->....tSugarCRMOutput_2(for updating)
on tJava_1, it is a output component, do nothing, just let it as empty.
on tJava_2, obbain the total number of processed data, for example:
int nb=((Integer)globalMap.get("tSugarCRMInput_1_NB_LINE"));
if(nb>0){
globalMap.put("hasData", true);
}else{
globalMap.put("hasData", false);
}
set the condition of runIf_1 as:
!(Boolean)globalMap.get("hasData")
set the condition of runIf_2 as:
(Boolean)globalMap.get("hasData")
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] If Condition Then Update Else Insert

Hi Shong,
thanks for your answer.
I put in practise your advices. But, how can i connect the tJava_2 component to a SugarCRMOutput one?
Seventeen Stars

Re: [resolved] If Condition Then Update Else Insert

For the purpose of filter and splice a flow please use the tMap component!
Send the input flow to the tMap and create 2 output flows. Set your filter conditions to each flow and connect the flows to an output component dedicated for inserts and the other output flow to an output component dedicated for updates.
One Star

Re: [resolved] If Condition Then Update Else Insert

Ok, thanks.
I succeed and wrote about it here: http://www.talendforge.org/forum/edit.php?id=120842
One Star

Re: [resolved] If Condition Then Update Else Insert

Hi,
I have the same problem. I want to insert or update with a condition but i don't understand the @jlolling solution. Please help me
Community Manager

Re: [resolved] If Condition Then Update Else Insert

Hi kfiadjigbe
Please read my previous post, if it does't fit your need, please describe your request with more details.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] If Condition Then Update Else Insert

Hi Shong,
Thank you for reply me. My request is how can i create a job that makes up this logic:
- Read one ROW on tfileInputXml and make query to the dataBase to know if the row exist or not.
The query will be something like that ("SELECT * FROM TERRAIN WHERE TERRAIN.EXTERNAL_KEY ='"+ROW.DOSS_REFE_ORIG+"' )
- If the ROW exist on the database, i will check now (if TERRAIN.LOGICAL_TOUCH = 0) then UPDATE with the ROW which is read in tfileInputXml
else if (TERRAIN.LOGICAL_TOUCH =1) then DO NOTHING
- if the ROW doesn't exist on the database, else insert ROW which is read in tfileInputXml on the dataBase.


So, I have to check every row which is tfileInputXml to know if insert, update or nothing.
My job is like that :
tFileInput2
|
| if (Insert) ??? ------- TERRAIN
tfileInputXml -------- tMap --------- tjavaRow -------- else (Update) ??? ------ TERRAIN
|
|
tOraclerow (query to find something)
|
|
tParseRecordSet
|
|
tJavaRow

I want to post an image, but I don't know how to do it. Thanks
Six Stars

Re: [resolved] If Condition Then Update Else Insert

I prefer this way:
tMap
|
|------- tSugarCRMOutput_forInsert
|------- tSugarCRMOutput_forUpdate
tSugarCRMOutput_forInsert:
1. set action as insert
tSugarCRMOutput_forUpdate:
1. set action as update
In tMap (for example):
1. create a variabile named "operation"
2. calculate the operation and assign the desired value to "operation" variable using if-then-else short version; "I" if you decide to insert current row and "U" if you decide to update (ex. input_row.color=="red"?"I":"U")
3. on every output flow, set the expression filter properly (Var.operation=="I" for tSugarCRMOutput_forInsert......)
bye
One Star

Re: [resolved] If Condition Then Update Else Insert

Hi, have a look at my example (if content = "Ohio" then insert else update):

in your tmap

Here a tutorial : http://www.talendforge.org/tutorials/tutorial.php?language=english&idTuto=9
One Star

Re: [resolved] If Condition Then Update Else Insert

You 're all the best. Everything is OK for me. Thanks for @Jcs19 and @gorotman