Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: HOWTO use "LIKE" operator in Database Value Lookup or Database Join steps

  1. #1
    Join Date
    Mar 2011
    Posts
    8

    Default HOWTO use "LIKE" operator in Database Value Lookup or Database Join steps

    I'm try to do a lookup where one string should have many cases in which it is a substring of the other, but I can't figure out how to add the "%" wildcard character.

    There is a like "Comparator" in the "Database Value Lookup", but the step errors out if I try '%Field1%' in the the lookup keys table. Is there a way to use the wildcard character to either of these steps?

  2. #2
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    Quote Originally Posted by Notre1 View Post
    I'm try to do a lookup where one string should have many cases in which it is a substring of the other, but I can't figure out how to add the "%" wildcard character.
    Only one match is expected from a lookup, though. You can accept the first match (default) or signal an error on more than one match in the Database Value Lookup step.

    Quote Originally Posted by Notre1 View Post
    There is a like "Comparator" in the "Database Value Lookup", but the step errors out if I try '%Field1%' in the the lookup keys table.
    The LIKE expression must come from the input stream, not from the lookup table.
    So long, and thanks for all the fish.

  3. #3
    Join Date
    Mar 2011
    Posts
    257

    Default

    If both tables are on the same database you can solve this with a bad ass query or you can use one of following methods (there could be more but this is what comes to mind atm).

    Depending on the dataset you can use a crazy solution like creating a cartesian product (warning this will probably be a lot of rows) and do the comparison in a regex, UDJC or javascript step.

    Or a more performant way (depending on your table sizes) is to first use a database input step to select all your string you will use in the like statement and feed those to a parameterized query. (use a looping job for this).

    like marabu stated all lookups steps are made to have only 1 match.

    Greetz,
    Hans

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.