Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: simple problem comparing two tables from different databases.

  1. #1

    Default simple problem comparing two tables from different databases.

    Hi

    I hope this doesn't get posted twice, as I have already typed it once,

    I have two tables with a common column called PLATE_NUM
    one table has more rows than the other, I just want to find which plates the smaller table is missing

    I have a table input from the larger table a Database lookup to the smaller table
    and I have checked the box 'do not pass the row if lookup fails" and fail on multi reults
    with a "write to log" as the error handle step

    nothing gets written to the error log
    the step metrics show read, write input for the Database lookup
    488082, 487789, 487789

    How do I find the problem Plate numbers

    Thank you

  2. #2

    Default

    You're close to a working solution;
    "do not pass the row if lookup fails" flag means, that te lookup step will do a "inner join", and return only rows with a existing entry in your smaller table. Untik this option will change the step to a "outer join". Now, all your rows are kept in the rowset. You have just to add now a filter step after the lookup, with a condition where your lookup column value is null (or equal to the defined default value in the lookup step). Pass these results to your logging step

  3. #3

    Default

    what's strange is if I leave the 'do not pass row if lookup fails' checked and I compare two colums that I don't expect to be equal it writes out to the error log

    what you suggested does not seem to work either,

  4. #4

    Default

    How did you connect your logging step? Still by the error handler? If yes - change this to my suggestion by using outer join->filer->log with just the rowset (without any error handler).

  5. #5

    Default

    Thank you very much!
    I wasn't using error handler but, I forgot to choose a type for the colums returned in the database lookup.
    without string it could not test for null

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.