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

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

Anonymous — Thu, 21 Jan 2016 13:14:04 GMT

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~

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

Anonymous — Sun, 31 Jan 2016 05:14:38 GMT

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.

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

chichibio — Mon, 01 Feb 2016 15:24:23 GMT

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

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

Anonymous — Tue, 02 Feb 2016 04:16:01 GMT

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

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

chichibio — Tue, 02 Feb 2016 08:58:31 GMT

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 :

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

Anonymous — Tue, 02 Feb 2016 09:57:35 GMT

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

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

chichibio — Tue, 02 Feb 2016 13:42:56 GMT

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

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

Anonymous — Tue, 02 Feb 2016 14:35:17 GMT

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.