Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Using results from one step in another step

  1. #1
    Join Date
    Dec 2006
    Posts
    20

    Question Using results from one step in another step

    Hi all,

    ok, this is my third try to get an answer on this topic. Hope someone with some more insights than me can give an solution. I try to make it as easy as possible.

    I have an SQL-Step (doing a Select count(*).... on table on database A ). The result of this count i want to use to update a specific row in table on database b.

    The problem is how to get the result from one step to the other??? Parameter?? Or additional step???

    Please help or i'll go crazy about this!!

    Thorsten

  2. #2
    pstoellberger Guest

    Default

    There are a few options how you could do that..

    1) Table Input step (select count(*) as mycount from xyz) >> SQL Step (update z set y = 1 where a = ? .. and put mycount in there to replace the ?)

    2) 2 Transformations in one Job
    Have a look at the samples in %KETTLE_HOME%\samples\jobs\evaluate-result-rows

    -paul

  3. #3
    Join Date
    Dec 2006
    Posts
    20

    Default

    Quote Originally Posted by pstoellberger View Post
    There are a few options how you could do that..

    1) Table Input step (select count(*) as mycount from xyz) >> SQL Step (update z set y = 1 where a = ? .. and put mycount in there to replace the ?)

    2) 2 Transformations in one Job
    Have a look at the samples in %KETTLE_HOME%\samples\jobs\evaluate-result-rows

    -paul
    If i have more than one parameter (i need additional ones for tabschema and tablename), where is the mycount result placed (Which number of ? is it, first last)? Is it necessary, that the "select count" has to run in an table input step?

  4. #4
    pstoellberger Guest

    Default

    Where else do you want to put your select count into?
    With the Table Input you can get several columns and for each row that comes out of there you can fire a custom SQL Statement.. so I think it's a good way to do what you want

    which "?" is replaced with what column in the stream depends on the order you define the input for the SQL step
    in the following example this means:
    ... column1 = a ....
    ... column2 = b....
    ... where column 3 = c...

    if you want to fire this SQL for each row that comes out of the table input task.. you need to set the "execute for each row" option
    Attached Images Attached Images  

  5. #5

    Default Getting Started

    Samatar

  6. #6
    Join Date
    Dec 2006
    Posts
    20

    Default

    Thats still not hitting the point. Perhaps i oversimplified my problem a little bit.

    The transformation is as follows:
    1.Table Input from database A: Reads Table with all Tablenames
    2. Execute select count(*) for each row from Step 1 on database B
    3. Update Table on database A with result from step 2

    So, i think i can't use Table Input for Step 2 as it is used in Step 1 already.

    Therefore i need to know how to tranfer a value between two SQL Steps

    Thanks so far.

    Thorsten

  7. #7
    pstoellberger Guest

    Default

    There is exactly what you are looking for in the samples directory
    .. \samples\jobs\process all tables\

    Just open all the files in there with PDI and you will understand... :-)

    -paul

  8. #8
    Join Date
    Dec 2006
    Posts
    20

    Default

    Just had a quick look at it. Yes, seems to me like the thing i try to do. But never thought it might be that complex.

    I will give it a try and modify it for my needs. Seems to be a good lesson for better understanding PDI.

    Thanks for your support so far.

    Thorsten

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.