Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: Getting transformation name and using in SQL Script

  1. #1

    Question Getting transformation name and using in SQL Script

    Hi all!

    I want to pass the name of a transformation to a second transformation. In the second transformation, I execute an SQL using the name of the previous transformation in a Execute SQL Script step. .

    Two questions:

    1. How can I pass the name of the first transformation to a second transformation? I am trying to set and get variables but I am not going very well ...
    2. How can i use the name of the first transformation in a Execute SQL Script in the second transformation. Can I do "insert into table_a values (${transformation_name}, 'abc', 15789)"?

    Thank you for your attention and cooperation of all.


  2. #2
    Join Date
    Feb 2009
    Posts
    321

    Default

    leo you have a few internal variables...

    ${Internal.Transformation.Name} = name of the trasnformation
    ${Internal.Transformation.Filename.Name} = name file of the transformation....

    so... used the set variable for save this in other variable example: previous_tr...

    in the next transformation use the step get variables ... recover the variable previous_tr and later, in the script I think... mmmm, I sense this should work:

    insert into table_a values ('${previous_tr}', 'abc', 15789);

    tell us later

    THP

  3. #3

    Default

    Tks THP!


    I getting the name in another transformation using set and get variable as you say, but the sql doesn´t work. When i put the ' ' ,
    SQL recognizes as a string. I tried with ${previous_tr} and just with previous_tr and it doesn´t work too.

    When it works i will post here.

    Leo

  4. #4
    Join Date
    Feb 2009
    Posts
    321

    Default

    don't forget to enable the option "sustitution variables"

  5. #5

    Question

    Using insert into table_A values (${Test}, 'OK' ) , where ${Test} is the variable name, I have this error:



    2009/08/05 12:06:57 - Execute SQL script.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : An error occurred, processing will be stopped:
    2009/08/05 12:06:57 - Execute SQL script.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : Couldn't execute SQL: insert into etmpTesteLog values (transf_CarregaDimensaoAcao, 'OK' )
    2009/08/05 12:06:57 - Execute SQL script.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) :
    2009/08/05 12:06:57 - Execute SQL script.0 - ERROR (version 3.2.0-GA, build 10572 from 2009-05-12 08.45.26 by buildguy) : The name "transf_CarregaDimensaoAcao" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.


    transf_CarregaDimensaoAcao is the transformation name!

    What i doing wrong?

  6. #6
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    You don't want the Execute SQL script for pushing data to temp tables.
    You want to use the Table Output step.

    Since you seem to be doing a status tracking, you need to generate a row of values such as:

    Transform | Status
    transf_CarregaDimensaoAcao | OK

    Then use the table output.

    ----------
    ***BAD IDEA FOLLOWS*** NOT RECOMMENDED ***

    If you are set on using the Execute SQL Script (which is slower and not designed for the purpose that you are using it), you need to quote the string with the transform name. ie.
    insert into etmpTesteLog values ('${Test}', 'OK' )

    PLEASE! Do not do it this way.

  7. #7
    Join Date
    Feb 2009
    Posts
    321

    Default

    I agree with gutlez and I will repeat again... THIS IS NOT THE BEST OPTION (execute the sql script)..
    I have done only for the purpose of clarification as it was to replace a variable in an "insert"...

    THP

  8. #8

    Default

    I used table output and it´s all ok. It works!

    Another doubt: i set a variable "result". When transformation run OK, then i create a variable result = OK (link the green hop), else i create a variable result = ERROR (link the red hop). There is the best way to get the result of a transformation?

    Tks a lot!

  9. #9
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Best way to get the result of a transformation?

    Are you set on a particular method?
    Personally, I would recommend setting up a transform log table, and use the built in functionality.
    http://wiki.pentaho.com/display/COM/...ional+Metadata

    You can then retrieve the status with a simple:
    select (relevant fields) from logTransforms where Transname='${prev_trans}' order by id_batch desc limit 1

  10. #10

    Default

    Great tip!

    This topic can be closed here. Helped me a lot and everything is resolved.

    Until next time my friends .

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.