Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Add constant in Lookup Key Column

  1. #1
    Join Date
    Nov 2013
    Posts
    5

    Default Add constant in Lookup Key Column

    Hi,

    I am new to using Pentaho.

    I am trying to use a lookup on the target table to fetch only the rows that have been loaded with max(version_id). Is there a way I can achieve this.

    eg... I get 100 records from source ... I used the keys to join it to the target lookup table and mentioned the values to return from lookup. But I just need to check for values with the most current data in lookup (based n version_id).

    Is there a way I can add constant in the lookup key columns?
    e.g Table field (version_id) comparator (=) field1 (5)

    Thanks

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

    Default

    DB Lookup is for returning 1:1 records.
    DB Join is for returning 1:M records.

    If you want to lose all the other fields, and just get a specific set back based on field1, you could alternatively use Table Input.
    **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

  3. #3
    Join Date
    Nov 2013
    Posts
    5

    Default

    Thanks gutlez for the quick response.

    Maybe I wasnt able to frame the question properly. I do understand that DB Lookup is for returning 1:1 records.

    I just wated to lookup values which has the most recent version_id ... lets say version_id= 2

    I have same values loaded in my target table. The most recent values can be obtained by filtering on version_id. I just wasnt sure how would we tell the lookup table to look only for the most recent value.

    Thanks

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

    Default

    Use an add constants step, then the constant is in your stream, and you can use the DB lookup the way you want it to.

    However, I would almost suggest doing a table input (select max(version_id) from Table) then cartesian join it to your stream for your constant... But then, if you're going to do that, why not just do it all in the table input: select .... from Table where version_id=(select max(version_id) from Table)
    **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
    Nov 2013
    Posts
    5

    Default

    Unfortunately I cannot use the constant step (as far as I understand) .. as I need to take the max (version_id).

    I believe the only way I can accomplish this is as you mentioned: Not using the target table as lookup ... rather using it as a seperate table and doing a sql override and then joining it with the source table using a joiner.

  6. #6
    Join Date
    Nov 2008
    Posts
    777

    Default

    So why can't you use this query in a Table input step as gutlez suggested?
    Code:
    select * from Table where version_id=(select max(version_id) from Table);
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

  7. #7
    Join Date
    Nov 2013
    Posts
    5

    Default

    I am not sure if we could use a SQL override in lookup (please let me know if we can).

    And my source table is different than lookup table (my lookup is on target table). The source doesnt have a version_id.

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.