Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Sql-Query information required in Kettle transformation

  1. #1
    Join Date
    Aug 2013
    Posts
    132

    Default Sql-Query information required in Kettle transformation

    Hi All,

    I have sql query select paymentnumber_id from payment_details_customers where status="active" ;

    the paymentnumber_id retrieved is to be used as parameter in another query like the below statement

    select card_number, property_number, customer_name from customer_survey_details where paymentnumber_id=${paymentnumber_id}

    How to achieve this in pentaho kettle.

    Can some one please guide me.
    It will be a great help.

    thanks,
    malibu

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Table Input steps can be cascaded.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Aug 2013
    Posts
    132

    Default

    Hi Marabu,

    Thanks for the answer,
    But I didnt quite understand the solutions. can you please explain me.
    It will be a great help.

    Thanks,
    malibu

  4. #4
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Instead of a single Table Input step you use an additional one and add a hop.
    You can configure the second step to use the first as input for it's SQL parameters - "Insert data from step".
    Don't forget to enable option "Execute for each row".
    So long, and thanks for all the fish.

  5. #5
    Join Date
    Aug 2013
    Posts
    132

    Default

    Hi Marabu,

    Thanks a lot it worked for me.

    Just for my information: previously i tried thi step using transformation-1 : table_input ---> set variable and in Transaformation2: Get_variables---->Table input( here i used in where condition value=?) and then and Job: start-->transformation1-->transformation2

    But when i run the job I got the error saying: 1 parameter was expected, but it received atleast 2.
    Can you please explain me where i went wrong, such that I will have some proper knowledge on vriable passing in Kettle.

    Thanks a lot.
    Malibu

  6. #6
    Join Date
    Aug 2013
    Posts
    132

    Default

    Hi Marabu,

    It worked for me, tahnks a lot.

    But For my information: previously i tried using the following steps.

    Transformation-1: table_input--->set_variables
    Transformation-2: get_variable---> Table_input ( in where condition i substituted like this " where values=?")
    then Job1: Start-->transformation1--->transformation2

    But when I run the Job it was saying an error explaining, 1 parameter was expected , but it received atleast 2..
    Can u please explain me in this where i went wrong such that I should have some proper knowledge on variable setting and getting for future purpose in kettle.

    Thanks a lot,
    malibu

  7. #7
    Join Date
    Apr 2008
    Posts
    1,771

    Default

    Hi.
    My guess is that you have more than 1 row as a result of your transformation-1.
    In transformation 2, edit its settings and check "Execute once for every input row".
    http://wiki.pentaho.com/display/EAI/...28job+entry%29
    -- Mick --

  8. #8
    Join Date
    Nov 2013
    Posts
    382

    Default

    Why not a simple sql?

    select card_number, property_number, customer_name from customer_survey_details where paymentnumber_id in (select paymentnumber_id from payment_details_customers where status="active" )

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.