CODEWITHSUNDEEP
pentahopentaho-spoonpentaho-data-integration
Darla
Darla

Reputation: 95

The table input step does not execute when variable substitution is done for the query in Pentaho

Below is the query.

select * from tablex where c_trans_date>= (select CONVERT(DATETIME,concat(1,'-',(substring('${SHORT_FILENAME}',charindex('.','${SHORT_FILENAME}',1)-5,3)),'-',substring('${SHORT_FILENAME}',charindex('.','${SHORT_FILENAME}',1)-2,2)),121)) and c_trans_date<=(select CONVERT(DATETIME,dateadd(day,-1,(select dateadd(month,1,(select concat(1,'-',(substring('${SHORT_FILENAME}',charindex('.','${SHORT_FILENAME}',1)-5,3)), '-',substring('${SHORT_FILENAME}',charindex('.','${SHORT_FILENAME}',1)-2,2)))))),121))

The error says

2017/08/18 10:25:54 - Spoon - Transformation opened.
2017/08/18 10:25:54 - Spoon - Launching transformation [STATEX_DATA_PULL]...
2017/08/18 10:25:54 - Spoon - Started the transformation execution.
2017/08/18 10:25:54 - STATEX_DATA_PULL - Dispatching started for transformation [STATEX_DATA_PULL]
2017/08/18 10:25:54 - Input table.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Unexpected error
2017/08/18 10:25:54 - Input table.0 - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException: 
2017/08/18 10:25:54 - Input table.0 - Couldn't get row from result set
2017/08/18 10:25:54 - Input table.0 - Conversion failed when converting date and/or time from character string.
2017/08/18 10:25:54 - Input table.0 - 
2017/08/18 10:25:54 - Input table.0 -   at org.pentaho.di.core.database.Database.getRow(Database.java:2374)
2017/08/18 10:25:54 - Input table.0 -   at org.pentaho.di.core.database.Database.getRow(Database.java:2344)
2017/08/18 10:25:54 - Input table.0 -   at org.pentaho.di.core.database.Database.getRow(Database.java:2322)
2017/08/18 10:25:54 - Input table.0 -   at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:247)
2017/08/18 10:25:54 - Input table.0 -   at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:138)
2017/08/18 10:25:54 - Input table.0 -   at org.pentaho.di.trans.step.RunThread.run(RunThread.java:62)
2017/08/18 10:25:54 - Input table.0 -   at java.lang.Thread.run(Unknown Source)
2017/08/18 10:25:54 - Input table.0 - Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting date and/or time from character string.
2017/08/18 10:25:54 - Input table.0 -   at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
2017/08/18 10:25:54 - Input table.0 -   at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4853)
2017/08/18 10:25:54 - Input table.0 -   at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1781)
2017/08/18 10:25:54 - Input table.0 -   at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1034)
2017/08/18 10:25:54 - Input table.0 -   at org.pentaho.di.core.database.Database.getRow(Database.java:2362)
2017/08/18 10:25:54 - Input table.0 -   ... 6 more
2017/08/18 10:25:54 - Input table.0 - Finished reading query, closing connection.
2017/08/18 10:25:54 - Input table.0 - Finished processing (I=0, O=0, R=0, W=0, U=0, E=1)
2017/08/18 10:25:54 - STATEX_DATA_PULL - Transformation detected one or more steps with errors.
2017/08/18 10:25:54 - STATEX_DATA_PULL - Transformation is killing the other steps!
2017/08/18 10:25:54 - STATEX_DATA_PULL - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Errors detected!
2017/08/18 10:25:54 - Spoon - The transformation has finished!!
2017/08/18 10:25:54 - STATEX_DATA_PULL - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Errors detected!
2017/08/18 10:25:54 - STATEX_DATA_PULL - ERROR (version 6.1.0.1-196, build 1 from 2016-04-07 12.08.49 by buildguy) : Errors detected!

BTW, It works fine when query is executed in SQLServer.

Upvotes: 1

Views: 1669

Answers (1)

AlainD
AlainD

Reputation: 6356

Ahhrggg... Oh No ... Please Nooooo ... DateTimeFormat runs in MS-server but not in PDI ... Not that one ... Not again...

First check the Enable Lazy Conversion checkbox on the Input Table step is NOT on.

If the problem persist, you can try to edit the connection. On the left panel, select Advanced and check/uncheck the Support timestamp data type.

If the problem persist, then you have to find out where. Read the table with a SELECT * and try to convert the Dates with an Select Value/Metadata step and play with the format.

If you still cannot find the bug, try to solve the problem in a more PDI style rather that in one sql. As far as I understand, the data format depends on the ${SHORT_FILENAME}, so you can use that variable to Switch/Case to the appropriate Select Value/Metadata/Date Format, and migrate your sub select in the corresponding Lookups and Filter Rows. [And please, make me the favor of running a test BEFORE complaining about bad performance].

Upvotes: 1

Related Questions