Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: setting/passing parameters

  1. #1
    Join Date
    Aug 2010
    Posts
    4

    Default setting/passing parameters

    Hello,

    Created 4 step transformation with With Kettle - Spoon Community Edition 4.0.0, with 2 databases: source - MS SQL, destination - MySQL:

    step 1 (Table Input):

    (from source)

    SELECT isnull( min(oid), -1) min_ins, isnull( max(oid), -1) max_ins from activity_journal where ins = 1 and table_name = 'test_table';

    step 2 (Table Input):

    (from source)

    select a.* from rp.test_table a join activity_journal b on a.id = b.id where b.ins = 1 and b.oid >= ? and b.oid <= ? ;

    step 3 (Output Input):

    (into destination)

    Insert into destination test_table.

    step 4 (Execute SQL script):

    (from source)

    delete from activity_journal where oid <= ? and table_name = 'test_table' and ins = 1;

    where expected ? to be replaced with max_ins (from step 1)


    The above steps are "joined" via hops as step 1 -> step 2 -> step 3 -> 4

    When executed there is an error reported on step 4:


    2010/08/09 11:37:36 - Cleans Activity Journal.0 - ERROR (version 4.0.0-stable, build 13376 from 2010-06-15 11.00.26 by buildguy) : Error finding field: max_ins]
    2010/08/09 11:37:36 - Cleans Activity Journal.0 - ERROR (version 4.0.0-stable, build 13376 from 2010-06-15 11.00.26 by buildguy) : Unexpected error :
    2010/08/09 11:37:36 - Cleans Activity Journal.0 - ERROR (version 4.0.0-stable, build 13376 from 2010-06-15 11.00.26 by buildguy) : org.pentaho.di.core.exception.KettleStepException:
    2010/08/09 11:37:36 - Cleans Activity Journal.0 - ERROR (version 4.0.0-stable, build 13376 from 2010-06-15 11.00.26 by buildguy) : Couldn't find field 'max_ins' in row!
    2010/08/09 11:37:36 - Cleans Activity Journal.0 - ERROR (version 4.0.0-stable, build 13376 from 2010-06-15 11.00.26 by buildguy) : org.pentaho.di.trans.steps.sql.ExecSQL.processRow(ExecSQL.java:127)
    2010/08/09 11:37:36 - Cleans Activity Journal.0 - ERROR (version 4.0.0-stable, build 13376 from 2010-06-15 11.00.26 by buildguy) : org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
    2010/08/09 11:37:36 - Cleans Activity Journal.0 - ERROR (version 4.0.0-stable, build 13376 from 2010-06-15 11.00.26 by buildguy) : java.lang.Thread.run(Unknown Source)

    My questions:

    1. Is this correct way to assign parameters as specified in step 1?
    2. Are there any other methods to assign parameters using SQL?
    3. Do you know why step 4 fails?

    With regards

    ys

  2. #2
    Join Date
    Aug 2010
    Posts
    4

    Default

    Hello,
    and Thank you if any looked into this. Finally figured this out. I've misinterpreted the concept of "parameters".
    With regards
    ys

  3. #3
    Join Date
    May 2009
    Posts
    10

    Default

    Hi ys,

    Could you please let me know how did you solve this problem.

  4. #4
    Join Date
    Aug 2010
    Posts
    4

    Default

    Hi Anupam,

    First added extra step (Set Variables) after step 1, which sets variables MIN_ID and MAX_ID (accordingly min_id, max_id from sql from step 1.).
    Second modified step 2, by using ${MIN_ID} and ${MAX_ID} instead of '?'.
    Third - converted steps into separate transformations, so step one executes as first one (to initialize variables).
    Hope it makes sense
    ys

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
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.