How to Pass parameter in table input?
I have one job with two transformation in it.
Transformation get list of data which is pass to another transformation. Here it execute for each row pass from first transformation.
In second transformation I have used
"get row from result" -> "table input"
In "get row from result" there are five field but in table input i have to use only 2th position and 3th position field.
even if i try to give single param "?" its giving error
" 2017/06/29 15:11:02 - Get Data from table.0 - Error setting value #3 [String] on prepared statement 2017/06/29 15:11:02 - Get Data from table.0 - Parameter index out of range (3 > number of parameters, which is 2). "
My query is very simple select * from table where col1= ? and col2 = ?
How can I achieve this? error? Is my doing anything wrong ?
Answer
You can also give names to your parameters, so that your query become
select * from table where col1="${param2}" and col2="${param3}".
Don't forget to check the "Replace variable in script" checkbox, and to adapt the quotes to your sql dialect (ex: '${param1}' for SQL-Server).
Note the param2 and param3 must exists in the transformation's Settings/Parameters, without the ${...} decoration and with values that don't break the SQL.
The values of the parameters can be set or changed in a previous transformation with a Set variables step (variables and parameters are synonymous in first approximation) and a scope at least Valid in the parent job.
Of course, if you insist in unnamed parameters for legacy purposes or any other reason, you are responsible to tell PDI that the first one is to be discarded, (eg where (? is null or 0=0) and col1=? and col2=?.