Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Database lookup for a unique row

  1. #1
    Join Date
    Jul 2007
    Posts
    18

    Question Database lookup for a unique row

    Hi,

    I have a process which goes likes this

    1. Read Excel spreadsheet and get the Name of the person
    2. Lookup that person in a table for their payroll number - this will only ever return one row
    3. Use that payroll id as a field within an insert table values (id (PK)-auto generated, payrollId)

    I have tried to use for step 3
    Insert/Update which does not appear to work because it relies on doing a query first to see if the row exists and I will have multiple payrollIds in this table.

    I have then tried the execute sql statement which gives me an error on the validation
    [4 - Error] Execute SQL script
    Step is receiving info from other steps but the SQL is only executed once!

    Am now not sure what to try, any advice would be appreciated.

    Also not sure why if I specify that it is an insert statement I still have to provide some value to look-up with.

    New to this part of the tool as you may have gathered

    Thanks

    Clemo

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

    Default

    Try table output.

    Regards,
    Sven

  3. #3
    Join Date
    Jul 2007
    Posts
    18

    Default

    I think I may be missing something fundamental here.

    When I try to use Table ouptut instead of Execute Sql I cannot make sense of the panel it gives me to fill especially when I press SQL and it starts talking about Altering my table. If I try to change the SQL I do not appear to be able to define a parameter. I honestly cannot see how to use this Table Output to help me, sorry. Just tried to look at some of the samples but they require MySql.

    Think I may have missed reading a document/chapter somewhere along the line and apologies if that is the case. If you can point me to the relevant document (have looked at User guide)

    Any help appreciated as this definitely looks an excellent tool, just think I need to understand it more

    Clemo

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

    Default

    For table output what you want to store in the table should also be what's in the row, so you have to upfront put the data right in the row. Instead of being able to pick which values you want to insert/update.

    Regards,
    Sven

  5. #5
    Join Date
    Jul 2007
    Posts
    18

    Default

    Hi Sven,

    Thanks for the info, I have managed to get it working by having a transformation that starts with three steps

    1. Excel Input
    2. Db Lookup
    3. Table Output

    Then I generated a mapping from the table output step and added to the transformation between steps 2 and 3 and it appears to work.

    Is this the correct way to do this or are there better ways? From your previous response I gathered I should use a Generate Row step but I could not get that to work at all, hence my concern I may not being doing this the best way.

    Thanks

    Clemo

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

    Default

    Where do I mention generate rows?... What you do seems to be ok.

    Regards,
    Sven

  7. #7
    Join Date
    Jul 2007
    Posts
    18

    Default

    Sorry Sven, it was my misunderstanding of your statement ".......so you have to upfront put the data right in the row"

    Thanks for your help much appreciated.

    Clemo

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.