djmlog103
02-01-2010, 08:44 AM
Hi,
I need to use data i got from a table input as parameters in another table input.
the 2 tables are of different databases.
For example
"Table input 1" may contain fields "field1".
can i use data produced in field1 as a parameter inside "Table input 2"?
I did try to do this. but i got an error.
here's what i did:
1. selected data using "Table Input 1"
SELECT
saleLineID as STsaleLineID
, saleID as STsaleID
, getCustomerIdFromSalesId(saleID) as STcustomerId
, getStoreIdFromSalesId(saleID) as STbranchId
, itemId as STproductId
FROM itemlines
2. hopped it to the second "Table input 2" and selectedthe step which its coming from and checked execute for each row.
SELECT
customer_key
, bt_key
, name
FROM customerdim
where customer_key = ?
But i get this error when trying to preview.
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : ERROR executing query: org.pentaho.di.core.exception.KettleDatabaseException:
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : offending row : [STsaleLineID Number], [STsaleID Number], [STcustomerId Integer(9)], [STbranchId String], [STproductId String]
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Error setting value #2 [STsaleID Number] on prepared statement (Number, setting [2586.0] on position #2 of the prepared statement)
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : java.sql.SQLException: Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : ERROR in part: P Set values
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Unexpected error :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : An error occurred executing SQL in part [P Set values]:
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : SELECT
customer_key
, bt_key
, name
FROM customerdim
where customer_key = ?
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : offending row : [STsaleLineID Number], [STsaleID Number], [STcustomerId Integer(9)], [STbranchId String], [STproductId String]
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Error setting value #2 [STsaleID Number] on prepared statement (Number, setting [2586.0] on position #2 of the prepared statement)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : java.sql.SQLException: Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.openQuery(Database.java:1796)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:214)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:127)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2889)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:345)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : offending row : [STsaleLineID Number], [STsaleID Number], [STcustomerId Integer(9)], [STbranchId String], [STproductId String]
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Error setting value #2 [STsaleID Number] on prepared statement (Number, setting [2586.0] on position #2 of the prepared statement)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : java.sql.SQLException: Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.setValues(Database.java:1058)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.setValues(Database.java:799)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.openQuery(Database.java:1730)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : ... 4 more
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Error setting value #2 [STsaleID Number] on prepared statement (Number, setting [2586.0] on position #2 of the prepared statement)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : java.sql.SQLException: Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.setValue(Database.java:1031)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.setValues(Database.java:1054)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : ... 6 more
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Caused by: java.sql.SQLException: Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at com.mysql.jdbc.ServerPreparedStatement.getBinding(ServerPreparedStatement.java:756)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at com.mysql.jdbc.ServerPreparedStatement.setDouble(ServerPreparedStatement.java:1694)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.setValue(Database.java:867)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : ... 7 more
2010/02/01 19:11:53 - Table input 2.0 - Finished reading query, closing connection.
where have i gone wrong.. ?
thanks in advance
I need to use data i got from a table input as parameters in another table input.
the 2 tables are of different databases.
For example
"Table input 1" may contain fields "field1".
can i use data produced in field1 as a parameter inside "Table input 2"?
I did try to do this. but i got an error.
here's what i did:
1. selected data using "Table Input 1"
SELECT
saleLineID as STsaleLineID
, saleID as STsaleID
, getCustomerIdFromSalesId(saleID) as STcustomerId
, getStoreIdFromSalesId(saleID) as STbranchId
, itemId as STproductId
FROM itemlines
2. hopped it to the second "Table input 2" and selectedthe step which its coming from and checked execute for each row.
SELECT
customer_key
, bt_key
, name
FROM customerdim
where customer_key = ?
But i get this error when trying to preview.
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : ERROR executing query: org.pentaho.di.core.exception.KettleDatabaseException:
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : offending row : [STsaleLineID Number], [STsaleID Number], [STcustomerId Integer(9)], [STbranchId String], [STproductId String]
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Error setting value #2 [STsaleID Number] on prepared statement (Number, setting [2586.0] on position #2 of the prepared statement)
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : java.sql.SQLException: Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - bdw - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : ERROR in part: P Set values
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Unexpected error :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : org.pentaho.di.core.exception.KettleDatabaseException:
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : An error occurred executing SQL in part [P Set values]:
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : SELECT
customer_key
, bt_key
, name
FROM customerdim
where customer_key = ?
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : offending row : [STsaleLineID Number], [STsaleID Number], [STcustomerId Integer(9)], [STbranchId String], [STproductId String]
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Error setting value #2 [STsaleID Number] on prepared statement (Number, setting [2586.0] on position #2 of the prepared statement)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : java.sql.SQLException: Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.openQuery(Database.java:1796)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.doQuery(TableInput.java:214)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:127)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.step.BaseStep.runStepThread(BaseStep.java:2889)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:345)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : offending row : [STsaleLineID Number], [STsaleID Number], [STcustomerId Integer(9)], [STbranchId String], [STproductId String]
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Error setting value #2 [STsaleID Number] on prepared statement (Number, setting [2586.0] on position #2 of the prepared statement)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : java.sql.SQLException: Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.setValues(Database.java:1058)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.setValues(Database.java:799)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.openQuery(Database.java:1730)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : ... 4 more
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Caused by: org.pentaho.di.core.exception.KettleDatabaseException:
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Error setting value #2 [STsaleID Number] on prepared statement (Number, setting [2586.0] on position #2 of the prepared statement)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : java.sql.SQLException: Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.setValue(Database.java:1031)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.setValues(Database.java:1054)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : ... 6 more
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Caused by: java.sql.SQLException: Parameter index out of bounds. 2 is not between valid values of 1 and 1
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at com.mysql.jdbc.ServerPreparedStatement.getBinding(ServerPreparedStatement.java:756)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at com.mysql.jdbc.ServerPreparedStatement.setDouble(ServerPreparedStatement.java:1694)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : at org.pentaho.di.core.database.Database.setValue(Database.java:867)
2010/02/01 19:11:53 - Table input 2.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : ... 7 more
2010/02/01 19:11:53 - Table input 2.0 - Finished reading query, closing connection.
where have i gone wrong.. ?
thanks in advance