Five Stars

Input to toracleinput query

Team, I have a scenario like below, tfileinputpositional --> toracleinput --> tfileoutputdelimited The input file has a column “EMP_ID”, with various values like 100,200,300. Now I need to pass that EMP_ID that i read from the file as parameter/input to toracleinput component, where I will be having a query like below, Select * from emp where emp_id = “should come from input flow”; How to achieve the above requirement, please advise.
  • Data Integration
Eleven Stars

Re: Input to toracleinput query

You can do it likes this....


 tfileinputpositional --> tFlowToIterate---iterate-->toracleinput --> tfileoutputdelimited


The tFlowToIterate will store each row from your file in globalMap variables. The globalMap is a HashMap. These are global variables so you can use them in your tOracleInput query. For every row returned from your file, your query will fire. Each time it fires it will fire using your data from the file. 


To alter the SQL wuery it is just simple Java String manipulation. For example....

From MyTable
Where col1 = '" +((Sring)globalMap.get("row1.value")) +"'"

You have to be careful to get the SQL syntax right. In the above example I have assumed your data value is from a column called "value" and the that the "row" passing to the tFlowToIterate is called "row1". Notice the single quotes I have surrounded the String globalMap with. The globalMap will store your values as Objects so you will need to cast them, as I have above.

Rilhia Solutions