topic Re: Result Query to context variable in Talend Studio

Result Query to context variable

chichibio — Tue, 13 Sep 2016 10:45:36 GMT

Hi,
I'would store the result of query in variable and use this variable in new query.
For example:
query: select max(a) from table
tMysqlInput-> in context variable.
I define context variable in this way:

wrong I ?
Thnx at all

Re: Result Query to context variable

JR1 — Tue, 13 Sep 2016 20:33:51 GMT

No need to use a context for this. You can achieve this by using e.g. tOracleInput/tMySqlInput/... --> tFlowToIterate --> tOracleInput/tMySqlInput/... --> (everything else you want to do). The first input represents your query posted above (select max(a) from table).
In tFlowToIterate you convert the input into a global variable which you can use in your next input like this:
"select XYZ from TABLE2 WHERE COLUMN = " + ((String)globalMap.get("a"))
(assuming that you named the column "a" in the schema of the first input and that it is of a numeric type).

Re: Result Query to context variable

chichibio — Mon, 19 Sep 2016 10:55:01 GMT

JoRoesecke wrote:
No need to use a context for this. You can achieve this by using e.g. tOracleInput/tMySqlInput/... --> tFlowToIterate --> tOracleInput/tMySqlInput/... --> (everything else you want to do). The first input represents your query posted above (select max(a) from table).
In tFlowToIterate you convert the input into a global variable which you can use in your next input like this:
"select XYZ from TABLE2 WHERE COLUMN = " + ((String)globalMap.get("a"))
(assuming that you named the column "a" in the schema of the first input and that it is of a numeric type).

Hi Jo...thank you so much to your suggest. It's very simple.
My job going execute fine, but no rows insert in my 2nd table:
I would compare the column_date to select new elements from 2nd table and insert/update in first table.
I would : select max(date) as a from table1->result in variable-> "select * from table2 where column_date >"+((String)globalMap.get("a"))
I think that is not string. Exist ((Date)globalmap.get("a")) ?
How I can do it?

Thank so much

Re: Result Query to context variable

Anonymous — Mon, 19 Sep 2016 12:24:36 GMT

Click on the "row1" link. Take a look at the schema. The tFlowToIterate will save the column data to the globalMap using the following format....

[rowname].[columnname]

So if your row1 link has a column called "myColumn", the tFlowToIterate will save it to a key of "row.myColumn". It is case sensitive.

So your query would be...

"select * from table2 where column_date >"+((String)globalMap.get("row1.myColumn"))

Re: Result Query to context variable

chichibio — Mon, 19 Sep 2016 15:13:51 GMT

rhall_2.0 wrote:
Click on the "row1" link. Take a look at the schema. The tFlowToIterate will save the column data to the globalMap using the following format....

[rowname].[columnname]

So if your row1 link has a column called "myColumn", the tFlowToIterate will save it to a key of "row.myColumn". It is case sensitive.

So your query would be...

"select * from table2 where column_date >"+((String)globalMap.get("row1.myColumn"))

thnx to your suggest. But exactly I had problem with string, so I change that in this way:
"select * from tabl2 where column_date > '"+((java.util.Date)globalMap.get("row1.column_date"))+"'";

Finally I had click on row1 but I selected only column_date without other column from table1.
I resolved with your suggest and other little change

thnx at all