Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: JOIN using CONCAT

  1. #1

    Default JOIN using CONCAT

    I need to reply this query:

    Code:
    SELECT *
    FROM a
    right outer join
    b
    ON b.field1 LIKE CONCAT(a.field2, '%')
    I don't know how kettle is able to reply LIKE and CONCAT in a join.
    Any idea?

    Thanks!

  2. #2

    Default

    Maybe I need to give more details.

    I have two tables:

    TABLE A
    field1 field2
    x aaa bbb
    y ccc ddd

    TABLE B
    field3 field4
    aaa w
    eee z

    I would like to make a join like this:
    field1 field2 field3 field4
    x aaa bbb aaa w

    Is it possible using Kettle?

  3. #3
    Join Date
    May 2014
    Posts
    358

    Default

    Sounds like an SQL problem more than a Kettle problem. Does the SQL query you have written work when you run it on the database directly (using SQL Developer or some such)?

  4. #4

    Default

    Quote Originally Posted by Lukfi View Post
    Sounds like an SQL problem more than a Kettle problem. Does the SQL query you have written work when you run it on the database directly (using SQL Developer or some such)?
    Yes, it does.
    I don't know how to set up this query using Kettle.

    I've used "split field" writing "aaa,ccc" as delimiter, but it's not a good idea if I would like to automate the process.

  5. #5
    Join Date
    May 2014
    Posts
    358

    Default

    So you don't want to do it in SQL and have a Table input step in Kettle, but to do it inside Kettle?
    If the field looks like "aaa bbb", then you can split it using space as a delimiter, then you can use something like a stream lookup or merge join to join the streams together.

  6. #6

    Default

    Quote Originally Posted by Lukfi View Post
    So you don't want to do it in SQL and have a Table input step in Kettle, but to do it inside Kettle?
    Yes!
    Quote Originally Posted by Lukfi View Post
    If the field looks like "aaa bbb", then you can split it using space as a delimiter, then you can use something like a stream lookup or merge join to join the streams together.
    Unluckly, it's not so simple. I have sentences instead of "aaa bbb", so I can not use space as delimiter.

  7. #7
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Not that it's a fast way to do it, but you could use a calculator step to do "incoming string"+"%" and then do a lookup with the "Like" operator.

  8. #8
    Join Date
    May 2014
    Posts
    358

    Default

    I don't see a fast way to do it, either. You could use the Database join step where you pass the "words" from Table B as a parameter and SELECT from Table A the rows where the text begins with them, but it essentially means that for every incoming row from Table B, the database has to do a full scan on Table A. Alternatively, you could read both tables using a Table Input, use Merge Join with no key fields to produce a cartesian join (not "Join rows (cartesian product)", that doesn't work on large data sets), and then a Java Filter step where you can do "fieldA2.beginsWith(fieldB3)".

  9. #9
    Join Date
    Apr 2008
    Posts
    4,696

    Default

    Quote Originally Posted by uilli View Post

    I would like to make a join like this:
    field1 field2 field3 field4
    x aaa bbb aaa w

    Is it possible using Kettle?
    Quote Originally Posted by uilli View Post
    I need to reply this query:
    Code:
    SELECT *
    FROM a
    right outer join
    b
    ON b.field3 LIKE CONCAT(a.field2, '%')
    I don't know how kettle is able to reply LIKE and CONCAT in a join.
    Any idea?

    Thanks!
    Your join statement won't give any matches as designed... You would need to do them the other way round: ON a.field2 LIKE CONCAT(b.field3, '%')
    The % means "anything" ... This means that "aaa" is not like "aaa bbb%" you need "aaa bbb" is like "aaa%"

  10. #10

    Default

    I've done a mistake writing the query.
    Thank you for your answers.

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.