Search code examples
sqlpentahokettledynamic-queries

Pentaho: String to SQL Select Statement

I have dynamic SQL that need to be manipulated before it is executed. Basically I built the query through passing as string. I finally came up with the query, but my problem now is how to execute the said query. I tried using a table input and the content of the SQL is just a '?' to be replaced on the built string. Unfortunately it does not work.

How do I do this?

Below is a screenshot of the steps. I may no need the Select values step, I just added that for visibility for myself. I'm quite new on pentaho kettle. But, I hope you get the idea. Basically what I did here is once I entered in the Calculator step, this is where I formulated the SQL query and, because it is a select, would like to execute it on the Table Input.

enter image description here

This is the error:

2015/04/15 21:45:09 - Table input.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Unexpected error
2015/04/15 21:45:09 - Table input.0 - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2015/04/15 21:45:09 - Table input.0 - An error occurred executing SQL: 
2015/04/15 21:45:09 - Table input.0 - ?
2015/04/15 21:45:09 - Table input.0 - ERROR: syntax error at or near "$1"
  Position: 1
2015/04/15 21:45:09 - Table input.0 - 
2015/04/15 21:45:09 - Table input.0 -   at org.pentaho.di.core.database.Database.openQuery(Database.java:1641)
2015/04/15 21:45:09 - Table input.0 -   at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:235)
2015/04/15 21:45:09 - Table input.0 -   at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:143)
2015/04/15 21:45:09 - Table input.0 -   at org.pentaho.di.trans.step.RunThread.run(RunThread.java:60)
2015/04/15 21:45:09 - Table input.0 -   at java.lang.Thread.run(Unknown Source)
2015/04/15 21:45:09 - Table input.0 - Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
  Position: 1
2015/04/15 21:45:09 - Table input.0 -   at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2101)
2015/04/15 21:45:09 - Table input.0 -   at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1834)
2015/04/15 21:45:09 - Table input.0 -   at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
2015/04/15 21:45:09 - Table input.0 -   at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:510)
2015/04/15 21:45:09 - Table input.0 -   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:386)
2015/04/15 21:45:09 - Table input.0 -   at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
2015/04/15 21:45:09 - Table input.0 -   at org.pentaho.di.core.database.Database.openQuery(Database.java:1611)
2015/04/15 21:45:09 - Table input.0 -   ... 4 more
2015/04/15 21:45:09 - getLastIncrementingPk - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Errors detected!
2015/04/15 21:45:09 - getLastIncrementingPk - ERROR (version 5.0.1-stable, build 1 from 2013-11-15_16-08-58 by buildguy) : Errors detected!
Solution

  • Got the answer. As I did and you all posted, after coming up with the SQL statement I put it in a variable, for exampl ${GEN_SQL}. That is Transformation 1.

    I then created another Transformation(2) to execute SQL. First I "Get Variable" step and then I used Table Input, as for the SQL I put there ${GEN_SQL}. Then checked "Replace variables in script".

    Then done, the SQL executed with the expected result.

    Thanks for the help guys.