One Star

How to pass a sql query result set to a variable in talend

Hi all~
Iam using talend 6.1 .
Part1: This is a simple scenario where I wanna do a count of employees and pass that value to a variable.
select count(emp_id) from Emp_Latest --10 --  I want to pass 10 to a variable.(var1)
part 2: Once that is done I want to check if that value is same as the count_of_employees data obtained from a flat file.
So once part1 is done , I can do the same after reading flat file , and pass required value to a variable(var2).
Do help on how to get part 1 done and how to check whether var1=var2 and only if it matches the job should  succeed else should fail.
In SSIS we can do that using execute sql task and can reconcile data using precedent constraint, I am looking for a similar function.

Thanks and Regards
Praveen U~ Smiley Happy
7 REPLIES
Community Manager

Re: How to pass a sql query result set to a variable in talend

Hi  
Execute the count query with tMSSQLInput and store the result to a context variable on  tJavaRow, the job looks like:
tMSSQLInput--main--tJavaRow
   |
onsubjobokb
   |
tFileInputDelimited_1--main--tJavaRow--runIf--tDie
on tJavarow:
context.nb_employee==input_row.columnName;
Set the condition of runIf:
!((Integer)globalMap.get("tFileInputDelimited_1_NB_LINE")==context.nb_employee)
tDie: stop the job and throw a customized error message.
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: How to pass a sql query result set to a variable in talend

Hi, I'have difficult to do it.
I have the first query "select max(date) from tab1". That result is necessary for the second query because will be part of conditions.
I want use the max(date) to select the record from tab2 like this:
"select * from tab2 join... where tab1.max(date) > tab2.max(date)"
How I can do it? I would use context variables or variables in tmap... help Smiley Sad Smiley Sad Smiley Sad Smiley Sad 
Community Manager

Re: How to pass a sql query result set to a variable in talend

Hi, I'have difficult to do it.
I have the first query "select max(date) from tab1". That result is necessary for the second query because will be part of conditions.
I want use the max(date) to select the record from tab2 like this:
"select * from tab2 join... where tab1.max(date) > tab2.max(date)"
How I can do it? I would use context variables or variables in tmap... help :( :( :( :( 

You can store the result of the first query to a context variable, and use this variable in the other query. For example:
tMysqlInput1--main--tJavaRow
  |
onsubjobok
  |
tMysqlInput2--main--tLogRow
on tJavaRow:
context.date=input_row.max_date_column;
on tMysqlInput2: write the query like this:
"select * from tab2 join... where tab1.max("+context.date+") > tab2.max("+context.date+")"
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: How to pass a sql query result set to a variable in talend

Hi, I'have difficult to do it.
I have the first query "select max(date) from tab1". That result is necessary for the second query because will be part of conditions.
I want use the max(date) to select the record from tab2 like this:
"select * from tab2 join... where tab1.max(date) > tab2.max(date)"
How I can do it? I would use context variables or variables in tmap... help :( :( :( :( 

You can store the result of the first query to a context variable, and use this variable in the other query. For example:
tMysqlInput1--main--tJavaRow
  |
onsubjobok
  |
tMysqlInput2--main--tLogRow
on tJavaRow:
context.date=input_row.max_date_column;
on tMysqlInput2: write the query like this:
"select * from tab2 join... where tab1.max("+context.date+") > tab2.max("+context.date+")"

Ok, the first step is difficult. How i can store the query result to a context variable. I want see the step.
I have that screen :
Community Manager

Re: How to pass a sql query result set to a variable in talend

 How i can store the query result to a context variable. I want see the step.
I have that screen :

I have showed you the job design as below:
tMysqlInput1--main--tJavaRow
  |
onsubjobok
  |
tMysqlInput2--main--tLogRow
on tJavaRow:
context.date=input_row.max_date_column;

//date is a context variable. If you have any troubles to get it works, please upload a screenshot of your job.

Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Six Stars

Re: How to pass a sql query result set to a variable in talend

 How i can store the query result to a context variable. I want see the step.
I have that screen :

I have showed you the job design as below:
tMysqlInput1--main--tJavaRow
  |
onsubjobok
  |
tMysqlInput2--main--tLogRow
on tJavaRow:
context.date=input_row.max_date_column;

//date is a context variable. If you have any troubles to get it works, please upload a screenshot of your job.

Best regards
Shong

Thnx for your patience, I'm beginner. Smiley Happy
I'm do it, but in tJavaRow I can't write context.date=input_row.max_date_column;
Dynamic setting is not available.
Best regards
David
Community Manager

Re: How to pass a sql query result set to a variable in talend

Hi
Write the java code in the basic setting panel rather than dynamic settings panel. Please refers to the component documentation and learn the basic usage of component. 
----------------------------------------------------------
Talend | Data Agility for Modern Business