Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Temp Table Question

  1. #1

    Default Temp Table Question

    ok, tried a couple variations on how to pull of this business scenario, and rather than give multiple sample implementations, simply going to ask the business question, and mention that 'execute sql statement' step does not seem to be a good solution ;-)

    1) Retrieve many rows of data from orig_table.

    2) Transform values in the stream from the orig_table.

    3) Save transformed values into a Temporary table (this begins the tricky part).

    4) Once complete all transformed values and everything ok (maybe a ModJS step to check some business rules) --
    4a) rename bak_table to deleteme_table
    4b) rename orig_table to bak_table
    4c) rename temp table to orig_table
    4d) delete deleteme_table

    The reason running into problems is the 'execute sql statement' step we have been using either make the calls on transformation load, or if set to 'on every row', will run on every row but not run on load. Doing the rename stuff on load breaks everything obviously ;-)

    Now, the other item slightly concerned about is some databases maintain the temp_table only during the transaction. Original thought about 3 transformations with a job, but not sure if that would handle all database types.

    Input please?


  2. #2


    Stored procedures mentioned here:

    Seem to be the only valid approach. Just replying to let others know that no other solution has been found.


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.