Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Execute SQL Script step_get the fields

  1. #1

    Default Execute SQL Script step_get the fields

    hello community,

    ganga here, I have been exploring kettle for while I'm getting a hang on it while I'm also facing few difficulties while doing it.

    I want use Execute SQL Script component, for SELECT Statement and my Query looks something like this....


    "SELECT value_1, value_2, value_3,_value_4 From Value_table Where value_date >= to_date(' ? ', YYYY/MM/DD')"

    When executed I didn't get the "
    value_1, value_2, value_3,_value_4" as the output fields... how do it get the selected column has output fields, Next the how can insert this value in to other table.


    __________________
    Thanks and Regards
    Gangadhar
    Thanks and Regards
    Gangadhar

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Use table input as the component to execute your query, not execute SQL.

    Regards,
    Sven

  3. #3

    Default Execute SQL Script step_get the fields

    Hi Sven,

    I was looking at your post and wondering if you got this issue resolved. I am also in the same situation, trying to get the data out from the execute sql script. But if I use the table insert, I am not able to pass the parameter.

    Could you please let me know?

    Thanks,
    Mani

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    In your case use database join or database lookup step.... btw you can pass in parameters to table input... parameters can be read from a input hop, if you filll in "insert data from step" and "execute for each row".

    And don't post your question multiple times

    Regards,
    Sven
    Last edited by sboden; 01-30-2008 at 03:23 AM.

  5. #5

    Default

    Sven,

    I have tried this several different ways and am not getting a Table Input step to work. I am using 3.0.2.

    I have a simple two-step transformation.

    First Step: Add Constants
    Name - MyVariable
    Type - String
    Value - Deductable

    Second Step: Table Input
    Query - Select col1 from mytab where col2 = '?'
    Replace Variables in Script - True
    Insert Data from Step - Add Constants
    Execute for Each Row - True

    2008/03/03 11:43:33 - Table input.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : Unexpected error :
    2008/03/03 11:43:33 - Table input.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : java.lang.NullPointerException
    2008/03/03 11:43:33 - Table input.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.trans.step.BaseStep.getRowFrom(BaseStep.java:1417)
    2008/03/03 11:43:33 - Table input.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.trans.steps.tableinput.TableInput.processRow(TableInput.java:92)
    2008/03/03 11:43:33 - Table input.0 - ERROR (version 3.0.2, build 538 from 2008/02/06 13:13:19) : at org.pentaho.di.trans.steps.tableinput.TableInput.run(TableInput.java:326)

    I have tried various ways of execution and none of them are working. If I change the variable type to Integer and provide the appropriate value and column to compare, I do get data returned, but the results are not correct (e.g. Value = 100 and I get values with 0).

    The other approach I was thinking of using was the SQL Execute and the delimit the output and use a splitter. This is a bit brute force, but it may get around the problem I am having with Table Input.

    Bill

  6. #6

    Default

    I have finally found that a Database Join step will work appropriately. I have never gotten the Table Input to accept parameters correctly nor have I gotten an Execute SQL Script step to work correctly. The Database Join step allows for an arbitrary SQL Statement and it provides the appropriate output stream. So, it seems to act as combination of the two.

    This seems to be working for me at this time.

    Bill

  7. #7
    Join Date
    May 2006
    Posts
    4,882

    Default

    For parameters to work with table input you need the 3.0.2 version.

    Regards,
    Sven

  8. #8

    Default Parameters

    I am using the join but it forces me to read 2 million records for afterwards drop all except the last week.

    I am using version 3.02

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.