Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: combining rows from a table without an exact foreign key.

  1. #1
    Join Date
    Oct 2007
    Posts
    10

    Default combining rows from a table without an exact foreign key.

    I have a table which contains a date field which is a starting date. Essentially i need to combine oltp record timestamp with the most recent matching starting date from this other table. example.

    create table payment_record (
    datestart date,
    payment decimal(18,2)
    );

    create table user
    (
    date_joined datetime,
    userid int,
    name varchar(255)
    );


    so i need to combine the latest datestart with the user. There isnt a field that directly joins the payment_record to the user table.

    What is the most effective way in kettle to accomplish this? My current approach is to use a stored procedure, but this is going to end up being much slower.

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

    Default

    Some nifty SQL is the best you're gonna be able to do.

    Regards,
    Sven

  3. #3
    Join Date
    Oct 2007
    Posts
    10

    Default

    Since the payment table is small i was hoping to load the entire table into kettle once and then using javascript to contain the logic of determining which row to use.

  4. #4
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    It's going to require a >= operator of some kind. The "stream lookup" would be nice to use if it would work.
    That being said, you might be able use the Join Rows step with a clever condition to do it.

    Matt

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.