Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Replacing Values with Ids

  1. #1

    Cool Replacing Values with Ids

    Hi All
    I am trying to figure out how to implement following use case in kettle.

    1- Suppose I have two tables Table1 and Table 2.

    Table 1 has the following structure.

    Id, Value
    1 val1
    2 val2
    ...

    Table2 has following structure.

    Table1value, id, ItsOwnValue
    val1 3 val3
    val1 4 val4
    val2 5 val5
    ...


    Now I want to have a third table which will be like table2 but instead of table1value i want its id e.g

    Table 3:

    Table1Id, id, itsOwnvalue
    1 3 val3
    1 4 val4
    2 5 val5
    ...


    Can anybody help me out how to accomplish it in kettle?

    Regards
    Shuja

  2. #2
    Join Date
    Oct 2007
    Posts
    107

    Default

    Just a simple SQL statement such as:

    Code:
    Insert Into Table3 (Table1ID, ID, ItsOwnValue)
    Select T1.ID,
             T2.ID,
             T2.ItsOwnValue
    From Table1 T1
    Inner Join Table2 T2 On T1.VALUE = T2.TABLE1VALUE

  3. #3

    Default

    so will i need to use Execute Sql script contol? is it possible by some other control?

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

    Default

    What about:

    Table Input (Select portion of the SQL statement below Select ... T2.TABLE1VALUE)
    Table Output (Insert into Table3 )
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  5. #5
    Join Date
    Oct 2007
    Posts
    107

    Default

    Sure you can but I don't see any advantage since this is pure basic SQL, unless it's because I don't have the whole picture, I don't see a reason to use other control which can only be slower at the end depending on the amount of records we're talking. (Assuming that both the input and output are sitting on the same database here; is it the case?)

    Quote Originally Posted by shujamughal View Post
    so will i need to use Execute Sql script contol? is it possible by some other control?
    Last edited by CHamel; 07-14-2010 at 02:28 PM.

  6. #6

    Default

    one quick question, i have used Table Input and it outputs table 2 records now what i want that i put some control and it outputs me in table3 format with id (no insertion at the moment so i can first check which records are new and insert only those). so the idea is

    TableInput (table 2)-> Some Control -> output in form of Table 3

  7. #7
    Join Date
    Sep 2007
    Posts
    834

    Default

    In Kettle-style:

    Table input for reading table2
    Database lookup for getting the id from table1
    Table output for inserting in table3

    If you have to do more work or data manipulation before inserting, or even before the DB lookup, this would be the way.

  8. #8

    Default

    Thanks Maria,
    It works.

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.