Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Multiple input parameters to SQL query to generate output files

  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Default Multiple input parameters to SQL query to generate output files

    Hi,

    I am a newbie to Pentaho PDI. I have the following problem: I have 4 input parameters (regions) to my Oracle query: east, west, north, south. The query is like:
    Select * from my_tab where region = upper('$region'); //(east, west, etc)
    The records from the four corresponding queries will be output to four text files, such as file_east, file_west, etc.

    I searched the Pentaho forum and tutorial and I am not clear how I should feed the parameters to the Table Input - SQL query and then from query to Text File Output. I tried to use Get System Info as input arguments for SQL query and it doesn't work.

    Thanks in advance.

    Ninja Li

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Do you have 4 values for 1 parameter, or 4 parameters?

    If you have this:
    Select * from my_tab where region = upper('$region');

    I assume 1 parameter, 4 values... where you have 4 input rows each with a value... in the table input use:

    Select * from my_tab where region = upper(?);

    - Fill in "insert data from step"
    - Execute for each row.

    --------------------

    If you have 4 parameters, you use four ?'s in your query and they would be binded positionally.

    You can also use variables, but that's mainly for other stuff... things which don't come from your input rows (as you can't use variables in the transformation where you define them).

    Regards,
    Sven
    Last edited by sboden; 07-17-2009 at 02:16 AM. Reason: typo

  3. #3

    Default

    To me it more looks like you will need four output files each fed from either one specific input component where you specify the appropriate region or fed from one query afterwards filtered with the appropriate region.
    With the filter you can split the single input data flow into four flows according to your region and direct them to the region file.
    You may excplicitly create a file with the region in its name or you may compute the file name with an expression using the region for the data flow.

    Michael

  4. #4
    Join Date
    Jul 2009
    Posts
    7

    Default

    Thanks a lot for your help. I really appreciate it.

    Yes, I have one parameter, with 4 values passed in. I am still not sure what kind of Input should I choose in Kettle to store the parameter to pass to the Table Input query?

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.