Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Problem passing variables through transformations using "Set and Get Variables" steps

  1. #1
    Join Date
    Oct 2011
    Posts
    6

    Default Problem passing variables through transformations using "Set and Get Variables" steps

    Hi there, I'm having some problems passing variables through 2 transformations. I'm working with a very simples example :
    1. 1 Job with 2 Transformations.
    2. Transformation 1 simply gets data from a table and sets variables with that data in a Set Variables step with the scope : Valid in the root job.
    3. Transformation 2 starts with a Get Variables step which gets my variables assigned in Transformation 1. After this there is a SQL SCRIPT step in wich I use some of those variables to pull data from another table in my database.
    When I run the Job, the variables that I'm trying to set in Transf 1 aren't accessible in my Transformation 2 for some reason. Besides that I also need to know how to access variables in a SQL SCRIPT, currently I'm using '${var}' sintax.

    Any help would be very much apreciated.exampleTest.zip

  2. #2
    Join Date
    Mar 2006
    Posts
    170

    Default

    Hi,

    In the "TRANSF_TESTE_GETROWS_FROM_RESULT" try keeping the variable as a String and NOT an Integer in Get Variables step.

    Then in the Execute SQL script step use the following syntax:


    SELECT
    idTFVENDAS
    , VENDAS_TOTAIS_DIA
    , TOTAL_ENCOMENDAS_DIA
    , VENDAS_HIST
    , ENC_HIST
    FROM TF_VENDAS
    where idTFVENDAS = '?'


    Good luck!

    Kent

  3. #3
    Join Date
    Oct 2011
    Posts
    6

    Default

    Hey kandrews, thank you for your input !!
    However is still does not work. Maybe I'm not understanding the big picture here. I mean, if using '?' in my sql script, should that mean that I can only use 1 variable per script ?? And do I really need the get variable step? In my Set Variable step in INIT_JOB_VARS transformation i defined the variable scope as "valid in root job". Doesn't this mean that the variables declared in this step are available in the whole root job (in my case, in the JOB_INIT_VARS job) ? I'm sorry for making so many questions but I want to get this straight =D !!

    Once again thanks for the help.

  4. #4
    Join Date
    Mar 2006
    Posts
    170

    Default

    Hi,

    How many values do you expect to be in "lastID" ... for each run?
    If it is more than one you should look in the samples dir for a job like "Process all tables" that shows how to do this type of scenario.

    Also why not just have a trans that has a Table Input Step that has the following SQL in it ...

    SELECT
    idTFVENDAS
    , VENDAS_TOTAIS_DIA
    , TOTAL_ENCOMENDAS_DIA
    , VENDAS_HIST
    , ENC_HIST
    FROM TF_VENDAS
    where idTFVENDAS IN (
    SELECT
    ultimoIDTF
    FROM REGISTOS_EXTRACOES
    )

    As I believe that will get you the same results as what you are doing?

    Not sure...

    Good Luck!

    Kent

  5. #5
    Join Date
    Oct 2011
    Posts
    6

    Default

    Hi !! Yes, your query gives me exactly the same results as what I'm doing =D! I'm just trying to work around the process of setting and accessing variables throughout a job and in its many transformations. I just used this 1 Job and 2 Transf as an example to understand it.
    Thanks again for your answers

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.