Hitachi Vantara Pentaho Community Forums
Results 1 to 10 of 10

Thread: OR in DB Lookup Step

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

    Default OR in DB Lookup Step

    Ok, so I've got a bad situation, and I'm trying to figure out the best way out of it...

    I have a field lets call it Status in two tables.

    One has a list of valid statuses:
    ID | Status | Abbreviation
    1 | Active | A
    2 | Loaned Out | LO
    3 | Repair | R

    The Other is user supplied free-form data which is supposed to reflect that data.
    Since they don't see the IDs, they use either the actual status of the Abbreviation.

    ID | Title | Copy | Status
    1 | Harry Potter - Philosopher's Stone | 1 | Active
    2 | HP-PS | 2 | A
    3 | Harry Potter - Philosopher's Stone | 3 | LO
    4 | The Real Book of Magic | 1 | Repair
    5 | A Sample of Spells | 1 | LO

    So what I need to do is load the user data to a table in the DB referencing the Status ID. But... The DB Lookup Step doesn't allow "OR" (ie. Data.Status = Status.Status OR Data.Status= Status.Abbreviation )

    What's the best way to work around this?

    I'm thinking of mappings, and using the error handling aspect of DB lookup.

    Here's where things get annoying though... In my source data, I have multiple columns which use the full name / abbreviation format.

    Someone give me a pointer?

  2. #2
    Join Date
    Nov 2008
    Posts
    777

    Default

    I think what I would do is use a Value Mapper to covert all the Full Names to Abbreviations in the free form stream before doing the DB Lookup in the Status table to find the Status ID.
    pdi-ce-4.4.0-stable
    Java 1.7 (64 bit)
    MySQL 5.6 (64 bit)
    Windows 7 (64 bit)

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

    Default

    Ahh... but that's what makes this fun
    Doing that would require scanning the table first to figure out what the valid abbreviations of the day are...

    I'm sure that there could be another use case for this, I just can't come up with it at the moment.

    I'll play with a few more of the steps and see if I can come up with something.

  4. #4
    Join Date
    Mar 2010
    Posts
    159

    Default

    ORs and parenthesizing could make the step more valuable... something like the filter rows where you can add subconditions etc. would be neat.

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

    Default

    Dang it... I see another JIRA in my future

  6. #6
    Join Date
    Dec 2009
    Posts
    609

    Default

    Hi,

    you could also use a "Database Join" Step instead of "Database lookup"
    In this step you have the possibility to outer-join the results (if the lookup fails)
    and use custom "OR"-SQL Statements. Combine this with the ?-parameter syntax and pass your fields to the statement....

    HTH,

    Tom

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

    Default

    Actually, for me, the DB-Join might just work...

    I'll try it out and see.

  8. #8

    Default

    If the tables are small (not a lot of rows) I would go for the solution you proporsed - lookup the status, in case it fails lookup with the abbreviation. Since you can use caching it is not a real performance hit.

    If the user data table is big, I would create a table which conforms your input data. Conforming = load all columns which have full name/abbrev + primary key --> conformed_user_table and transform the data to 'full name'. Having a kind of change data capture which does the transformation only on rows which changed since the last load would be a good idea.

    kind regards
    Max

  9. #9
    Join Date
    Oct 2006
    Posts
    7

    Default

    Maybe just create a view with a 'union all' in your DB over the status table and use that in the DB lookup?

    create view status_union_all as
    select ID, Status
    from status
    union all
    select ID, Abbreviation
    from status

    Regards,
    JJ

  10. #10
    Join Date
    Dec 2009
    Posts
    609

    Default

    JJ,

    you can do this in the "Database Join" step directly... without having to create a view

    Best regards,

    Tom

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.