Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Multiple Variable, is possible?

  1. #1
    Join Date
    Jul 2012
    Posts
    24

    Default Multiple Variable, is possible?

    Hi all,
    I've a question?

    I have a SQL Table on MySQL with some anagrafics about brands companies.
    The table is like this

    +----------+
    |ID|"Brands"|
    |1 | MyComp |
    |1 | Pentaho|
    |1 | Kettle |
    |1 | Food |
    |1 | Cola |
    |1 | A&C

    Now i want to set a variable ${BRANDSNAME} and set it with the value of the culumn in the DB, 'cause in a transform I have a query( and other process from file) that need to do it for each vale of the table Brands.

    For example in an other transform I want to do some like this:

    Select * from product where
    Manufacturer =
    ${BRANDSNAME}
    and in another step Filter For where a field value =
    ${BRANDSNAME}

    So the transforma has different result based on the value of
    ${BRANDSNAME}

    FOR EACH ROW.

    Is possible set up a variable dinamically? Someone can explain me how to do it?
    Regard.
    Gaetano.


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

    Default

    When using the "Execute SQL Script" step you can switch to SQL variables instead of PDI variables:

    Code:
    SELECT * FROM product WHERE manufacturer = ?
    Just tell the step to read its argument for substitution from a "Table Input" step that you configure to give the brand names.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Jul 2012
    Posts
    24

    Default

    Ok, but not only for SQL query, i need to replace, the same value, also in anoter "filter row step" so I prefere storege the value in a variable, the problem is that I want to set the variable different times, for each value of the brands table. I need a parameter to use in other step ( and this parameter should be the same for alla transform).

    For example, If i execute SQL SELECT * FROM product WHERE manufacturer = "Pentaho"
    after I've to "filter Row" where field1 = "Pentaho",
    and after I've to match some field in another step still with "Pentaho" value, so the parameter should be the same in all transform but i need to set up it dinamically for each t-uple of the brands table.
    Do you know what I mean?

  4. #4

    Default

    Create a job J1
    Call the trasformation T1
    In trasformation T1 read the table and save the row to result.
    Create a job J2
    In job J1, after the trasformation T1 call the job J2 and set this to repeat for each row.
    In job J2 call a new trasformation T2 that read the result and set the variable, then call a new trasformation T3 that do the stuff.

    Regards

    Bruno Condemi
    Last edited by bruno.condemi; 09-15-2012 at 04:16 PM.

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

    Default

    Gaetano, I assume that you know how to loop over the result set of a transformation using another transformation.

    What I still don't understand is:

    When you use "Get rows from result" and execute your transformation for each row, your product table is partitioned into brands. I can imagine a lookup or join step somewhere down the line, but no filter (or other) step desperately in need of a PDI variable, that must contain a value, that already is known from the single row coming in from "Get rows from result".

    But never mind, all ways lead to Rome...
    So long, and thanks for all the fish.

  6. #6
    Join Date
    Jul 2012
    Posts
    24

    Angry Almost resolved but just the a last problem

    Hi, I've almost resolved my problem, I follow the suggestions of Matt and Bruno. Now I've the problem because I'm not able to use the dinamic parameter( the one for each row) in the filter row step, instead I can in the SQL statemant.


    Please, I attach my files and check it, if someone can, where is my wrong?
    the parameter are setted in T1 and T2, and the job J1 call for each row the job J2 in the T3 u use the parameter, see the T3 transforma file to check, I need to filter with the same parameter used in SQL statement, I use the getSystemInfo step but it doesn't work.
    Please help me! I'm going to go crazy.
    Regards.
    Attached Files Attached Files

Tags for this Thread

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.