Loop in Kettle/Spoon/Pentaho

Question

I have a query like this:

SELECT count(distinct ID) FROM TBLC WHERE date BETWEEN ? AND ?;

I am using Pentaho Spoon. I am using 'Execute SQL Script'/ statement. The options I see are Execute for each row, execute as a single statement and variable substitution.

If I need to change my query or need other steps to implement, please response.

EDIT:

I am using a Pentaho Spoon to transfer data from Infobright database (table1, table2) to Infobright database (table3).

Query is similar to:

SELECT table1.column1, table2.column2
FROM table1 JOIN table2 ON table1.id=table2.id
WHERE table2.date BETWEEN '2012-12-01' AND '2012-12-30'

I want a way so that I do not have to manually specify the date range each time I run the transformation. I want to automate the date range.

Thanks in advance.

Answer 1

Based on what you've described, I believe you can accomplish what you want by using a generate rows step to inject rows into the stream containing the dates you want, then generate the needed queries for each date row in the stream to get all the rows you want from the source tables.

Answer 2

You can use execute as a single statement and variable substitution as they are best suited for your use case.

Add parameters StartDate and EndDate to your transformation and use them in your query as show below. Enable "Variable Subsitution" in the Execute SQL Script step.

SELECT table1.column1, table2.column2
FROM table1 JOIN table2 ON table1.id=table2.id
WHERE table2.date BETWEEN **$StartDate** AND **$EndDate**

Suppy values of StartDate and EndDate while executing the transformation.

Answer 3

i guess the dates are in a table or a file in the database what you can do is : create a job that get those parameters to the steam and set variables . on the next job you can use them as variable to your query using {date_from} {date to}

that way each time you run the jobs it takes what inside the database you of course need to update the date_from and date_to