Hitachi Vantara Pentaho Community Forums
Results 1 to 8 of 8

Thread: Database-Lookup with OR condition

  1. #1

    Default Database-Lookup with OR condition

    Hi,
    I'm trying to add some values to the datastream using an Database-Lookup-step.
    In SQL the where-part of the update-statement would be like
    Code:
    ...select t.VALUE1, t.VALUE2, t.VALUE3
    from LOOKUPTABLE t join DATATABLE d
    on  t.COND1	= d.COND1
    and t.COND2	= d.COND2
    and t.COND3	= d.COND3
    and (t.SEX	= d.SEX	or  t.SEX=-1)
    and (t.STATUS = d.STATUS	or  t.STATUS=-1)
    The problems are the lines with t.SEX and t.STATUS.
    The join-condition 't.SEX = d.SEX' has to be ignored if the value in the lookuptable is -1. Same thing with STATUS.

    Is there a way to this in a transaction?

  2. #2
    Join Date
    Feb 2009
    Posts
    296

    Default

    What about using UNION?
    Fabian,
    doing ETL with his hands bound on his back

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

    Default

    Where is the stream data in the below SQL command?

    There are other options than just the DB lookup...

  4. #4

    Default

    Hi,
    Sorry, I try to make it clear:
    My stream data is loaded from 'DATATABLE' and enriched during the transformation with some fields (e.g. STATUS & SEX).
    The VALUEx-fields from the 'LOOKUPTABLE' are needed to do some additional calculations.
    An alternative working way, I already implemented is:
    Transformation 1
    • to load the data
    • make the enrichments
    • write the Data back to the table

    Transformation 2
    • do an SQL-update with the described where-part on the database

    Transformation 3
    • to load the data again
    • do the calculations
    • write the Data back to the table again
    ... but this takes too much time ...

    My 'DATATABLE' has about 6 million rows, the 'LOOKUPTABLE' contains about 3 million rows. So I can't do something like a merge join in memory.

    Any ideas?

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

    Default

    What about using (as fabianS suggested) using a Union, or a join in a table input step?

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Well, there *is* a "DB Join" step...

  7. #7

    Default

    Quote Originally Posted by gutlez View Post
    What about using (as fabianS suggested) using a Union, or a join in a table input step?
    Hi,
    I have to figure out the parameter for the join (eg. SEX) during the transformation. So I can't use them in the table input step.

  8. #8

    Default

    Quote Originally Posted by MattCasters View Post
    Well, there *is* a "DB Join" step...
    Hi Matt,
    I hoped the cache functionality in the 'DB Lookup' step could accelerate the transformation.
    I think, the statment in the the 'DB Join' is fired for every row?

Tags for this Thread

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.