Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Using Get table name & Table Input to read data from all tables in d/b

  1. #1
    Join Date
    Mar 2016
    Posts
    1

    Default Using Get table name & Table Input to read data from all tables in d/b

    Hi,
    Just starting out out using Pentaho and despite reading loads of threads cant quite find the answer I'm looking for.

    I'm trying to get the names of all the tables in database all of which have a common field called Race_ID

    Then I want to read through each table extracting all the Race_ID data into one output table.

    I've started off using a Get table names process box which appears to do the job I need from what I see in Preview

    Then I thought I would be able to use a Table input box and it would roll through the tablename's from the previous step using the tablename as a parameter as the table name in the SQL like this

    select Race_ID from ${tablename}

    if the process box params are set up like this
    Name:  Capture.PNG
Views: 509
Size:  6.6 KB

    The error seems to indicate it not substituting the variable tablename into the Table input box for use in the SQL statement. What am I doing wrong? Thanks

    2016/03/24 12:57:24 - Table input.0 - An error occurred executing SQL:
    2016/03/24 12:57:24 - Table input.0 - Select race_id from {tablename}
    2016/03/24 12:57:24 - Table input.0 -
    2016/03/24 12:57:24 - Table input.0 - offending row : [tablename String(500)]
    2016/03/24 12:57:24 - Table input.0 -
    2016/03/24 12:57:24 - Table input.0 - Error setting value #1 [String(500)] on prepared statement
    2016/03/24 12:57:24 - Table input.0 - Parameter index out of range (1 > number of parameters, which is 0).
    2016/03/24 12:57:24 - Table input.0 -
    2016/03/24 12:57:24 - Table input.0 -
    2016/03/24 12:57:24 - Table input.0 -

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

    Default

    If you want to use a Kettle variable in your SELECT statement, the correct syntax is ${tablename}.
    You can read fields from a previous step to populate SQL parameters in a prepared statement, but you can't read those fields to achieve variable substitution.
    You'll need a job that runs a transformation to retrieve the tablenames (Get-Table-Names => Copy-Rows-To-Result).
    Then you can run the a second transformation for each result row setting (e.g.) variable TABLENAME from field tablename.
    Now you can use Variable substitution in your Table-Input step.
    So long, and thanks for all the fish.

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.