topic Re: [resolved] If Condition Then Update Else Insert in Talend Studio

[resolved] If Condition Then Update Else Insert

Anonymous — Sat, 21 Sep 2013 14:47:01 GMT

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

Re: [resolved] If Condition Then Update Else Insert

Anonymous — Sun, 22 Sep 2013 10:48:39 GMT

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

Re: [resolved] If Condition Then Update Else Insert

Anonymous — Sat, 26 Oct 2013 18:41:32 GMT

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?

Re: [resolved] If Condition Then Update Else Insert

Anonymous — Wed, 30 Oct 2013 19:37:19 GMT

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.

Re: [resolved] If Condition Then Update Else Insert

Anonymous — Wed, 30 Oct 2013 20:02:31 GMT

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

Re: [resolved] If Condition Then Update Else Insert

Anonymous — Fri, 06 Jun 2014 12:20:08 GMT

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

Re: [resolved] If Condition Then Update Else Insert

Anonymous — Wed, 11 Jun 2014 02:06:13 GMT

Hi kfiadjigbe
Please read my previous post, if it does't fit your need, please describe your request with more details.
Shong

Re: [resolved] If Condition Then Update Else Insert

Anonymous — Wed, 18 Jun 2014 10:38:35 GMT

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

Re: [resolved] If Condition Then Update Else Insert

gorotman — Wed, 18 Jun 2014 14:49:35 GMT

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

Re: [resolved] If Condition Then Update Else Insert

Jcs19 — Wed, 18 Jun 2014 15:21:37 GMT

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

Re: [resolved] If Condition Then Update Else Insert

Anonymous — Thu, 19 Jun 2014 12:07:25 GMT

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