Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Implementing Loops in Pentho

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Sep 2017
    Posts
    12

    Default Implementing Loops in Pentho

    I would like to run a certain SQL query on a subset of dates (every iteration with a different date value) Any idea how to implement it?

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

    Default

    Just do as documented:

    Use a parameterized WHERE clause in your Table-Input step like "WHERE date_column = ?" and let Table-Input read the dates from its input stream.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Sep 2017
    Posts
    12

    Default

    Thank you for the reply.

    I already have the SQL query working with parameters. The thing is that it fetches the value via Set variables object and it can hold only one value.

    How do I repeat this whole job over and over again, while also changing the content of the variable? (every iteration with different date)?

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

    Default

    I was talking about SQL parameters, you talk about Kettle variables.

    With variables your transformation must run once for each variable value.
    SQL parameters can accept multiple values in the same run, technically producing a union of multiple SQL result sets.

    So, what exactly have you done?

  5. #5
    Join Date
    Sep 2017
    Posts
    12

    Default

    I'm using "Table input" to fetch the data from the DB then "Set variable" to store the data.

    AFAIK, it can only hold one value? Am I right? If this is the case then I need another component to implement a loop.

  6. #6
    Join Date
    Nov 2013
    Posts
    382

    Default

    As marabu suggested, don't use variables, use parameters.

    Step 1: Table input
    select MyDate
    from Mytable
    where Myconditions

    Step 2: Table input. Check execute for each row box
    select whatever_you_need
    from needed_tables
    where date_on_table = ?

    The ? acts as a place holder that will be substituted by the value coming from the previous step.
    Last edited by DepButi; 10-19-2017 at 10:59 AM.

  7. #7
    Join Date
    Aug 2016
    Posts
    290

    Default

    On a side note: What is the difference between variables and parameters? In other development environments, these words may very well be synonyms (they can have the same meaning, be used interchangeably).

  8. #8
    Join Date
    Nov 2013
    Posts
    382

    Default

    Variable: value assigned to some internal storage than can be used across steps/transformations . They are defined on its own (using Set variable step or java)

    Parameter: value passed from one step to another one. You must define them on the step itself. The value does not span across steps/transformations

    In fact, the solution I suggested doesn't use variables neither parameters. ? is a place holder, PDI substitutes its value from the incoming row in order: first ? is first field, second ? is second field, etc It's clearly diferent from variables and parameters because both have fixed values for all the rows processed in the step, while ? gets the value for each row.

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.