Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Trigger a Table Input without the use of dummy parameters?

  1. #1
    Join Date
    Aug 2016
    Posts
    267

    Default Trigger a Table Input without the use of dummy parameters?

    In this example a transformation starts with a Table Input step. However, I only want to execute this select query in certain situations. So I create a step before that, doing some logic and sending a dummy value to the table input streams IF the query should be executed. The Table Input step is then configured using "Insert Data from step" and "Execute for each row". Great! However, the transformations crashes because dummy value is not used as a parameter in the sql query! Why is this usage strictly enforced?

    The solution was to make dummy logic for the dummy parameter (value always set to static '1') inside the sql, for example:

    ...
    WHERE 1 = ?


    Is there some way to get the same behavior without polluting sql code with this dummy garbage?

  2. #2
    Join Date
    Apr 2008
    Posts
    4,663

    Default

    None that I'm aware of.
    Table Inputs are designed to run independently as soon as possible. You would need to have some sort of flag into the SQL to indicate if it's supposed to run... otherwise, the Table Input will run. I suppose you could (since it's open-source) fork the step, and add the logic for "Only run if <condition>" but that would get really ugly really quickly -- how do you program in all the logic from all the other potential steps? I suppose you could do "Only run when first row arrives" to the step. If you defaulted that to false, you could probably even file a pull request to bring it into the mainstream step.

  3. #3
    Join Date
    Aug 2016
    Posts
    267

    Default

    Yes, thanks for reply. I don't want something ugly as you mention, but to have something of the type you describe at the end would be good. I'm not in a position to contribute in the source code at the moment unfortunately.

  4. #4
    Join Date
    May 2016
    Posts
    267

    Default

    Shouldn't make more sense to control whether the Table input (and the rest of the transformation) should run in a job? Create a transformation or a logic control in a job to determine if the transformation with the table input should launch.
    Regards
    OS: Ubuntu 16.04 64 bits
    Java: Openjdk 1.8.0_131
    Pentaho 6.1 CE

  5. #5
    Join Date
    Aug 2016
    Posts
    267

    Default

    Yes, in normal situations. But this case is actually a sub-transformation (the parent is also a transformation). I used "Simple Mapping" and noticed the sub-transformation would always be executed.

  6. #6
    Join Date
    Sep 2016
    Posts
    18

    Default

    why don't you try something like this?


    Write your SQL code in "Modified JavaScript value" or ("Add constants" + "Replace in string") and then pass that sql to "Dynamic SQL row" with predefined template.
    By this you can control what sql to run.


    Ravik

  7. #7
    Join Date
    Aug 2016
    Posts
    267

    Default

    That's very interesting, thanks!

    Positive:
    -Don't need dummy logic
    -More intuitive execution with rows in stream triggering sql (as opposed to the info steps)

    Negative:
    -Large SQL query strings are more messy to declare in java or javascript (as opposed to clean SQL only in Table Input step)

    Do you have recommendations for making a clean looking sql query in java or java script? The one I'm using in this case is 66 lines...

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 - 2017 Pentaho Corporation. All Rights Reserved.