0

I have created transformation which includes table input,sql step and excel o/p step.

  1. Table input-->Run a query and get the field "query" which includes sql query select * from dual

  2. Execute sql step-->Dynamically passing that query field using '?' and enabling variable substitution

  3. Excel o/p-Expecting o/p is the sql query should be triggered and get the result in excel o/p

But i can't get the fiels from execute sql step.. How i can do this???

Thanks
Kavitha S

JonasGao
  • 215
  • 1
  • 2
  • 10
user3007361
  • 63
  • 1
  • 2
  • 7

2 Answers2

2

Use Database join instead of Execute SQL step. The Database Join step allows you to run a query against a database using data obtained from previous steps.

  • Database join Input: You can pass any of data you want from previous step using ? notation in SQL query defined inside the step.
  • Database join Output: Executes parametrized SQL query and adds new parameters as an output.

The step is what you need for your 2nd step. See more info about the Database join step in the documentation.

mzy
  • 1,754
  • 2
  • 20
  • 36
0

In PDI, "Execute SQL Step" is not meant for generating rows. It will not add any extra row to the data stream. You got Table Input step to generate multiple rows.

What you can try as an alternative is to break the transformation into two parts.

Part 1: Table Input Step > (query rows are generated) >> Use "Set variables" or "copy rows to result" to some other steps to set the query into some variable e.g: query.

Part 2: Take another Table Input Step (into a next .ktr file) and use the variable substitution of ${query} >> Finally output the result set to the excel output.

For dynamically sql queries, you can read this blog.

In case you have some lookups to do with the query generated, you can use Dynamic SQL row to generate the rows.

Hope it helps :)

Rishu Shrivastava
  • 3,745
  • 1
  • 20
  • 41