Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: beginner's question ... sorry: How to lookup the max value and to use it for insert?

  1. #1

    Default beginner's question ... sorry: How to lookup the max value and to use it for insert?

    Hi folks,
    Sorry for this question, but I can't figure out, how to achieve this, did several trials, with dblookup, with sql script, sorting ...
    Why can't I achieve this with an sql sript, just by select max(value) from .. and returning this value, adding 1 by javascript or calculator etc.? I don't get the value as output field from the sql script step.
    If I'm using the dblookup, I can't figure out, how to use the max(value) within the step. From my understanding, I can only compare to an already known value. But from there I should implement a sort step, use the last row and add 1. That is for sure not the way to go ;-)

    The primary key I wish to fill is not a sequence. Is the only alternative a sequence? So I must modify the postgres database structure? Hm, I don't like this idea, because it's an out of the box erp implementation ...

    What would be the best strategy/methode for this? Yes, I know, simple issue, but I don't get any progress!
    Thanks a lot, Alexander

  2. #2
    Join Date
    Nov 1999


    Try using a "DB Join" step.
    Then try to run something like

    SELECT ID, max(field)
    FROM foo
    Then you can join that data in on ID as well. (Merge Join, Stream Lookup, etc)

    Take care,

  3. #3

    Default why don't I get output fields from a sql script?

    Hi Matt,
    Thanks a lot for your feedback. Great to hear from you :-) This medium is great, to have the opportunity to get in touch with the founder and the genious in behind ... even in our case, long time no seen :-)
    But to get back to your outlined direction, ok, I will use this, makes sense to me and should be the solution I was looking for. But one question / side remark:
    I started from the beginning to use/implement a sql script step. Because I like the idea of Kettle's way to document based on the GUI, what is going on, tried to run the group / sort / add based on Kettle steps. But for this purpose I would require the output / stream from the sql script.
    But it seems, there is no output?! I tested it with 3.1 and 3.2. So this is intended to be like this, correct? Why this, because from my understanding I can't use a sql script within a transformation as intermediate step, only as a single step or last step.

  4. #4
    Join Date
    Nov 1999


    Alexander, I never mentioned the "Exec SQL" step. Try again with the "DB Join" step. ;-)

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.