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

[Anonymous]

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~

[Anonymous]

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.

[chichibio]

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         

[Anonymous]

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+")"

[chichibio]

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 :

[Anonymous]

 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

[chichibio]

 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. 
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

[Anonymous]

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.