Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: SQL Query Formation

  1. #1
    Join Date
    Oct 2017
    Posts
    4

    Post SQL Query Formation

    In a transformation, I have created a PROPERTIES file with a set of variables, fetching those variables through properties step, denormalizing the rows with row denormalization and setting the variables with set variable.
    So, now when I am passing the variable that is set to table input step I am unable to form the SQL query.
    Firstly, the way that I am trying is possible.?
    If yes, can I get the approach please.
    Thanks in advance.

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

  3. #3
    Join Date
    Oct 2017
    Posts
    4

    Default

    I am not using the variables in the same transformation. I am setting the variables using set variables in a transformation using properties input and row denormalizer and sending to the other transformation using transformation executor step and in the transformation mentioned in the transformation executor step, getting those variables using get variables and when i print those using alert statements the variables are getting accessed, but, when i m using them to form query it is not taking the variables as input.. "select * from ${var}" is my query for table input in the child transformation and var is the variables that i'm getting in the child transformation from parent transformation.

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

    Default

    You have a variable V available to a transformation, but a reference to ${V} in step Table-Input doesn't get substituted?
    If you have enabled option "Replace variables in script" and still witness this behavior, you encountered a bug.

  5. #5
    Join Date
    Oct 2017
    Posts
    4

    Default

    Quote Originally Posted by marabu View Post
    You have a variable V available to a transformation, but a reference to ${V} in step Table-Input doesn't get substituted?
    If you have enabled option "Replace variables in script" and still witness this behavior, you encountered a bug.
    Yes, I did check that box.. But I am using the variable of one transformation in another child transformation.. Is that possible.
    A variable in the same transformation is able to be used.. but in my case i need to pass variables from one parent transformation in to the connected child transformation.
    Can you help with this if this is possible..?

  6. #6
    Join Date
    Aug 2016
    Posts
    290

    Default

    Quote Originally Posted by Praneetha B View Post
    Yes, I did check that box.. But I am using the variable of one transformation in another child transformation.. Is that possible.
    A variable in the same transformation is able to be used.. but in my case i need to pass variables from one parent transformation in to the connected child transformation.
    Can you help with this if this is possible..?
    Passing variables and the use of child transformations is poorly documented and not easy to understand the details of. I suggest you try with some examples and see what the behavior is like.

  7. #7
    Join Date
    Oct 2017
    Posts
    4

    Default getting variables from one transformation to another

    testGetVar.ktrtestSetVar.ktr
    In the above transformations I am setting variables in one(testSetVar.ktr) and getting in another(testGetVar.ktr)
    In testGetVar.ktr, in table input step I'm unable to get the variable in replace for the tablename.

    This is the .properties file
    JDA_DB_HOSTNAME=localhost
    JDA_DB_PORT=3306
    JDA_DB_NAME=jd_alm
    JDA_TB_NAME=jd_sponsors
    JDA_DB_USER=root
    JDA_DB_PASS=pwdd

    Help needed.
    Thanks In advance

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

    Default

    In TestSetVar,ktr, remove the 'Copy Rows to Result' and 'Transformation Executor', and remove the _s from the variable names.
    In TestGetVar.ktr, remove the 'Get Rows from Result' and 'Get Variables'
    Now create a Job, that has Start -> Transformation(TestSetVar) -> Transformation(TestGetVar).
    Save all of them.
    Run the Job.

    Parameters *are* variables. (They are a special case of them)
    Columns *are NOT* variables.
    Variables, in both their Set and Get phases must use the same names *EXACTLY* -- case matters.

    For further reading, open up the samples in %PDI_INSTALL_DIRECTORY%\samples\jobs\process all tables
    Last edited by gutlez; 10-27-2017 at 11:36 AM.

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.