CODEWITHSUNDEEP
pentahokettle
DGA
DGA

Reputation: 268

PDI Table Iinput don't send values to next step

Please guide me to a corect solution.

I have a transformation like this (in Kettle - Pentaho PDI):

CSV INPUT -> 2. Modified javascript value -> 3.Table input -> 4. Text file Output

1.CSV INPUT - i have a csv file with 4 columns (A,B,C,D) and i pass to next step only first 3 columns (A,B,C)

2.Modified javascript value - here i do some transformation int to string for this 3 columns and pass to next step

3.Table input - here i have a select like this one:

    SELECT column1, column2, column3, column4
      FROM tablename
      WHERE column2 = ? (this will be A)
      AND column3 = ?  (this will be B)
      AND column6 = ?  (this will be C)
      AND column8 = 'something'

4.Text file Output - corect result.

In previous model all are OK, but when i try to add params as column name in query Table input no pass results to next step... and no error in log. In log i see running sql and counting values, but no result pass to next step.

Query in Table input:

    SELECT 
      column1, 
      column2, 
      column3, 
      column4,
      ? columnX  (this will be A)
      FROM tablename
      WHERE 
          column3 = ?  (this will be B)
      AND column6 = ?  (this will be C)
      AND column8 = 'something'

As i sad i saw running query,no error and no result pass to next step.

Thank you! Geo

Upvotes: 1

Views: 1399

Answers (1)

Brian.D.Myers
Brian.D.Myers

Reputation: 2518

I haven't tried it this way, but I don't believe column names can be parameterized in a Table Input step. In this case I'd lean towards dynamically building the SQL in the transform, then executing it with a Dynamic SQL row step. This step takes an SQL previously built by the transform, runs it, and joins the results the the input row.

Note this is a regular database style inner join (unless you check the 'Outer join' checkbox). If one row goes in and causes a query to result in 3 rows, 3 rows are output from the step.

Also, note what the Docs say about template SQL. It's what provides the step's meta data to the rest of the transform. Note also that any column that gets chosen as ColumnX must be the same type as all others, and must be the same as the corresponding column in your template SQL. You can't for example select an int in one statement, then a date in another.

A final note: you don't need the JavaScript step just to convert ints to strings. Just change the type of the column in the input step to string. The way it is now, you're converting to an int, just to immediately convert back to a string.

Upvotes: 1

Related Questions