Generate SQL for table input

[mscahill]

Is it possible to do some scripting to generate a SQL statement for a table input? Here's what I want to do:

-Run an SQL statement to lookup the year
-Script a for loop that generates a SQL statement based on an offset from that year
-Run the SQL as a table input
-Feed the rows to a table output

Is this possible? Thanks in advance.

[ingo.klose]

Yes it is, but you need a job an two transformations.

The first transformation does the lookup, creates the SQL script (e.g. with the JS step) and puts it in a variabe, e.g. ${MY_SQL_SCRIPT}.

The second transformation has the Table Input step with a valid connection, the ${MY_SQL_SCRIPT} in the SQL box and the replace variable option activated.

Regards,
Ingo

[mscahill]

So what would that look like? Would the javascript just be a string declaration, ex:

var SQL
SQL = "SELECT * FROM mytable"

and then put this in the table input:

${SQL}

???

[ingo.klose]

No, you have to put SQL field in the stream (press Get variables at the bottom of the JS step dialog) and the put it into a KETTLE variable using the Set Variable step.

The table input should be ok.

[mscahill]

I did this, and got the error:

"There was an error executing SQL: ${SQL}
Syntax error for DATE escape sequence '{SQL}'"

[ingo.klose]

Did you activate the replace variable option?

If yes, try to output the SQL statement created and check the syntax. If possible post your 2nd transformation.

Regards,
Ingo

[mscahill]

I'm just working on the second transformation right now, using a static value for the year. This is how I have it structured for the time being.

[sboden]

sigh ... you can't reliable set variables in the same transformation as where you use them.

It's not the first time this is mentioned... it probably won't be the last.

Regards,
Sven

[mscahill]

So I need two transformations and a job to do this?

[mscahill]

I split it out, and I still get the same error