US and Worldwide: +1 (866) 660-7555
Results 1 to 3 of 3

Thread: using table input data as parameters in another table input

  1. #1
    Join Date
    Jul 2008
    Posts
    3

    Default using table input data as parameters in another table input

    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.
    Code:
    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
    Last edited by djmlog103; 02-01-2010 at 11:03 AM.

  2. #2

    Default

    Your seconnd step requires 1 parameter but you send 5 parameters to that step (the 5 fields of itemlines).
    You have to create two branches of your stream, one with the parameter, 2nd with the rest, do the query in one branch and then merge the streams again.

    I hope you understand my sample :-)

    But i think you don't really need to use this construct with table input, you should use the database lookup step.
    Attached Images Attached Images
    Last edited by MUE2000; 02-02-2010 at 04:43 AM.

  3. #3
    Join Date
    Jan 2013
    Posts
    1

    Default

    The best solution I have heard for this is create 2 transforms
    In transform 1 create the variable and store it using Set Variable step ( variable = variable)
    In transform 2 fetch using Get Variable variable = $variable
    and then call the sql using select * from table where column = '${variable}'
    Call both from a job one after the other.
    This has worked for me.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •