Search code examples
kettlepentaho-spoon

Kettle PDI how to define parameters before Table input

I'm converting data from one database to another with a slightly different structure. In my flow at some point I need to read data from the first database filtering on the id coming from previous steps.

This is the image of my flow enter image description here

The last step is where I need to filter data. The query is:

SELECT e.*,UNIX_TIMESTAMP(v.dataInserimento)*1000 as timestamp
FROM verbale_evento ve JOIN evento e ON ve.eventi_id=e.id
WHERE ve.Verbale_id=? AND e.titolo='Note verbale'

Unfortunately ve.Verbale_id is a column of the first table (first step). How can I define to filter by that field?

Right now I've an error: 

2017/12/22 15:01:00 - Error setting value #2 [Boolean] on prepared statement
2017/12/22 15:01:00 - Parameter index out of range (2 > number of parameters, which is 1).

I need to do this query at the end of the entire transformation.

Solution

  • You can pass previous rows of data as parameters.

    However, the number of parameter placeholders in the Table input query must match the number of fields of the incoming data stream. Also, order matters.

    Try trimming the data stream to only the field you want to pass using a select values step and then choose that step in the “get data from” box near the bottom of the table input. Also, check the “execute for each input row”.