Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Insert and Select Statement

  1. #1

    Default Insert and Select Statement

    Hello,

    I am lost on how to realize a simple insert and select statement using Pentaho ETL.
    Assuming I have 4 tables like below and want to realize the following query:

    Insert into table4(time_key,location_key,machine_key) select time_key,location_key,machine_key from table1 t, table2 l, table3 m where m.location_name = l.location_name or l.OP-date = t.date

    What are the best steps to implement it?



    Table 1 has 3 columns:
    machine_key machine_name location_name
    1 cutting-ps london
    2 polisher-td Brisbane
    3 washer-tx Florida

    Table 2 has also 3 columns:
    location_key location_name OP-date
    22 Brisbane 1999-01-05
    24 Atlanta 2003-09-07
    28 Sydney 2008-03-03

    Table 3 has 4 columns:
    time_key year month day date
    33 1999 1 5 1999-01-05
    - - - - -
    - - - - -
    2877 2008 4 1 2008-04-01

    Table 4 has 3 columns:
    time_key location_key machine_key

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

    Default

    If all tables are in 1 database... running the select statement in 1 table input with a hop to e.g. an update/insert step.

    Regards,
    Sven

  3. #3

    Default

    Quote Originally Posted by sboden View Post
    If all tables are in 1 database... running the select statement in 1 table input with a hop to e.g. an update/insert step.

    Regards,
    Sven
    Hello Sven,

    Thanks a lot for the information. But what of a situation where the tables are on different databases?

    Best regards,

  4. #4
    Join Date
    Jan 2008
    Posts
    22

    Default Use "Merge Join" steps

    Even if they're all 4 on different databases, it's a pretty simple transformation - check out "Merge Join - Join 2 sorted data sets.ktr" in the "samples" directory

  5. #5

    Default

    Quote Originally Posted by tml View Post
    Even if they're all 4 on different databases, it's a pretty simple transformation - check out "Merge Join - Join 2 sorted data sets.ktr" in the "samples" directory
    Thanks for the info.

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.