CODEWITHSUNDEEP
etlpentaho
Tony Zhu
Tony Zhu

Reputation: 301

Table input insert data from step

When I use table input and insert data from step to replace ? in the sql.

For example,

select * from a where b in (?).

from mysql log, the sql is select * from a where b in ('0,1,2,3')

how can make it be executed without ' ?

Thanks

Upvotes: 2

Views: 10052

Answers (1)

Brian.D.Myers
Brian.D.Myers

Reputation: 2518

There are three possible approaches I see. I'm guessing you like option 'C' the best.

A) If you know you will always have only 4 values for your IN clause, you can do it by storing the 4 values in Job variables and reading them into a single row with a Get Variables step and then flowing that row into a Database Lookup step whose SQL looks like this:

select * from a where b in (?, ?, ?, ?)

and substitute the values from the single incoming row.

B) If you have a variable number of values, I would consider normalizing the list of values into rows, perhaps with a Split Fields to Rows step, adding a 'found' value to each row with an Add Constants, then flowing those into a Stream Lookup. Then after the Stream Lookup, flow off all the rows 'found' value is null to a Dummy step.

C) Build your value list in a Job and pass it in as a variable. In your Table Input step use a statement like this:

select * from a where b in (${varname})

and check 'Replace variables script'. The variable will be expanded into the SQL before it is executed.

Upvotes: 1

Related Questions