Passing values to a global veritable and call it inside a tMSSQLInput component

[Tasfiahm]

Let me explain the complex problem.

1. In my main job I am reading the data from a Oracle table and extracting EMPLOYEE_ID as numeric values.

2. So In my subjob I have a SQL Server tMSSqlInput component to select the data. where my sql look like this:

Select name,employee_id

from emplyees

where employee_id In(.............................................)

I can't use a join between the two database.

So if I want to put/store the values of the 1st SQL component output in a variable and call it inside the 2nd subjob SQL how i can do it.

So now I want to use the output value of the 1st job inside the where clause of the 2nd job's sql

[TRF · Accepted solution]

tJavaFlex to construct the IN clause from the 1rst select statement and store or into a global variable. Then reuse this variable for the 2nd select. Pay attention of possible query length limitation.
An other approach could be to make the join between both tables inside a tMap.

[Tasfiahm]

Thanks a lot for your response. But as I have mentioned earlier I cannot join the two tables because they are coming from two different database and this IN condition is inside an inner sub query of the 2nd table.

Let assume the from the 1st query the data is coming as below
Employee_id
1
2
3
5
7
how I can store the the values in a tJava component as
1,3,4,5,7

and called the variable inside a query.

FYI, I am able to write a the result of the 1st query/component in a .csv file. Then I am using another sub job to read the .csv file using tFlowToItterate to put the value in a global variable and then call the variable in the 2nd query/component. But now i want to store the value in a variable and call it in the 2nd query. My solution is workable but it is not the most efficient