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
    Posts
    9,729

    Default

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

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

    Take care,
    Matt

  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.
    Cheers,
    Alexander

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

    Default

    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.