Hitachi Vantara Pentaho Community Forums
Results 1 to 9 of 9

Thread: dynamic columns

  1. #1

    Default dynamic columns

    Hello Folks,

    I've tried to use PDI to pass a query to my oracle database but i got a problem.
    My query should be dynamic cause i dont know the fields i want to retrieve.

    In this case i've tried the step Table Input but it doesnt work for field, works fine for values substitution.

    Does anyone know how to do that?

    Thanks

    Vegas
    www.ademargomes.com

  2. #2
    Join Date
    Jun 2010
    Posts
    114

    Default

    Attachment 5930

    I am not sure if this helps but,
    The one which striked my mind when thinking about dynamic parameters is the Database Join step
    You can give dynamic parameter fieldnames in this step
    I think, you need to connect the Database Join step to your table input step.

    Plz find the screenshot attached.
    Last edited by omm; 08-11-2010 at 03:53 PM. Reason: Better answer
    Thanks,
    Om
    ----------------------
    Spoon version -4.4.0

  3. #3

    Default

    Tks Om,

    But i've tried with Database Join but it doesnt work either
    Does anyonw know how Dynamic SQL step works?

    Thanks

  4. #4
    Join Date
    Jun 2010
    Posts
    114

    Default

    The execute SQL script steps looks similar to Database Join step where the ? get replaced by the dynamic parameters we give.
    But, I have not tried it yet.
    Let's wait for a reply who has a better understanding

    Good Luck
    Thanks,
    Om
    ----------------------
    Spoon version -4.4.0

  5. #5

    Default

    Om,

    The point is that execute SQL script uses substitution just for values, if i want to set the name of the columns i need, it doesn't work. Apparently it is a JDBC standard
    But thanks man! thanks a lot.

  6. #6
    Join Date
    Feb 2009
    Posts
    321

    Default

    vegas you must set variables in a previous transformation... yesterday I was in a similar situation, and worked fine
    try something like this..

    job
    --1st transformation
    -- 2nd job (enable "execute for every row")

    1st transformation
    text input --> copy rows to result

    format of text input (both of datatype string):
    column; value


    2nd job
    1_transormation
    2_transformation

    1st transformation
    --> get rows from previous result --> set variables ( here set column and value)
    2nd transformation

    --> table input --> other steps...
    in table input replace the variables in this way..

    select * from tabla
    where ${column}= '${value}'

    is only an example... and works for me

    good luck
    Hernan

  7. #7

    Default

    Hey Hernan,

    Thanks man, i'll try and post here the results.

  8. #8
    Join Date
    Feb 2009
    Posts
    321

    Default

    your welcome, if you have problems to develop this, I will attach the jobs and transformations
    good luck again
    Hernan

  9. #9

    Cool

    Hello guys.

    In the end we solve this using pl/sql procedures on oracle.

    Thanks for all the help.


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.