title: How to get a resultset from a dynamic select-based sql query in Pentaho-Kettle?
body: <p>I want to execute a set of select-based sql queries, derived from  xml-node elements within a XML file, and write the values of the corresponding resultsets in a CSV file. I'd like to clarify that no field of the sql query is parameterized, but the full sql query itself is.</p>
  
  <p>The part of getting the full sql query is done as expected, but I don't know how to proceed with the part of launching the sql query so that I can get the corresponding resultset to manage it later.</p>
  
  <p>What I've tried until now with no success is the following:</p>
  
  <ol>
  <li><p><code>Execute SQL script</code> step: according to what it's said <a href="https://stackoverflow.com/questions/28534640/execute-sql-step-in-pentaho">here</a>, this sort of step doesn't get any resultset.</p></li>
  <li><p><code>Dynamic SQL Row</code> step: I get the corresponding sql as long as I hardcode the field 'TemplateSQL', but I can't do that because the sql queries derived from the previous step are very different among them and don't provide a fixed meta-data scheme.</p></li>
  <li><p><code>Table Input</code> step: this step only allows single values of a sql query to be parameterized, not the full query.</p></li>
  </ol>
  
  <p>I'd really appreciate if some pentaho-kettle expert could tell me the right way of accomplishing the task I want to do.</p>
  
  <p><strong>Update (following @Cristian Curti instructions):</strong>
  The problem arise in KTR-1. When you say <em>"you have to pass this query as a variable to another KTR"</em>, I understand that what I have to do first is setting the xml-node value in a variable and then pass it to the KTR-2. To do so, in my KTR-1 I'm using these steps:</p>
  
  <ol>
  <li><code>Get Data from XML</code>: I'm getting 'SQLQuery' field.</li>
  <li><code>Set Variables</code>: I'm setting the field 'SQLQuery' in a variable named 'QUERY'.
  (Moreover, KTR-1 is set as you said, that is, in the Parameters tab I set a parameter named 'QUERY').</li>
  </ol>
  
  <p>When I preview the second step of KTR-1, the Kettle console logs the following error:</p>
  
  <pre><code>Get data from XML.0 - Finished processing (I=2, O=0, R=0, W=2, U=0, E=0)
  Set variables.0 - Setting environment variables...
  Set variables.0 - Set variable QUERY to value [        
    SELECT "Table1".*, "table2"."field1" FROM "Table1" INNER JOIN "Table2" ON Table1"."PATIENT" = "Table2"."PATIENT" WHERE ("Table1"."field1" &gt; Table1"."field2") AND NOT("field1" BETWEEN 'date1' AND 'date2')     
  ]
  Set variables.0 - ERROR (version 8.2.0.0-342, build 8.2.0.0-342 from 2018-1-14 10.30.55 by buildguy) : Unexpected error
  Set variables.0 - ERROR (version 8.2.0.0-342, build 8.2.0.0-342 from 2018-1-14 10.30.55 by buildguy) : org.pentaho.di.core.exception.KettleStepException: 
  Set variables.0 - Only 1 input row was expected to set the variables and at least 2 were received.
  </code></pre>
  
  <p>It seems <code>Set Variables</code> step only expects to receive one single row.
  I don't know if I'm doing something wrong or missing something. In case you need further details, just tell me. Thanks.</p>