Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Database Lookup fails when null Values

  1. #1
    Join Date
    Aug 2010
    Posts
    29

    Default Database Lookup fails when null Values

    Hello,

    I'm trying to do a database lookup but when I get null values, the database lookup does not return anything.

    Is there any way to handle this?

    Thanks

    Lophophora

  2. #2
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    The only way to handle that would be filter your rows before they go to the DB lookup.
    You cannot look up "" and expect it to find something, unless you have "" in your DB to look up.

    We might be able to provide a bit more help if you give us something more to work on.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  3. #3
    Join Date
    Aug 2010
    Posts
    29

    Default

    What I'm Trying to do is simple;

    I have this in the database;

    Product
    | id | name | price | Size |
    | 1 | someProduct | 20 | null |

    then I have this on the stream
    name="SomeProduct", price=20, Size = null

    Then into a "Database Lookup" into the table product, it compares name, price and size and it has to return id=1, but instad it return id=null or the default value of the id configured in the "DB Lookup" step.

    I need to compare eventhough values are null. Was I more clear?

    Thanks

  4. #4
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    If the name, price, Size are your key, and you are looking up the ID, consider trying the Combination Lookup step instead.
    What you are trying *SHOULD* work (as I recall having done it), so perhaps if you turn up the logging level you can see what's going on.
    **THIS IS A SIGNATURE - IT GETS POSTED ON (ALMOST) EVERY POST**
    I'm no expert.
    Take my comments at your own risk.

    PDI user since PDI 3.1
    PDI on Windows 7 & Linux

    Please keep in mind (and this may not apply to this thread):
    No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.
    Signature Updated: 2014-06-30

  5. #5
    Join Date
    Sep 2010
    Posts
    15

    Default

    Three valued logic dictates that null can never equal null.
    That is, a null size will never equal a null size so you database lookup won't work when the size is null.
    Solutions:
    1) Make the database default the size to zero when it is null and do the same conversion in your data stream; or
    2) Put in an intermediate view and use coalesce to change nulls to zero (or some other value) and do the same in your data stream; or
    3) Use a database join or a merge join to gain more control over how nulls are used in the join.
    4) I've also used a concatenated business key in data warehousing that, in your case would push name, price and size together into a single key (separated with '|' or ';'). This simplifies the dimension lookup pattern by always matching on a single key and there are never any nulls to worry about. To do this, add a column to the table or add this business key to an intermediate view.
    Last edited by JasonHogg; 09-27-2010 at 08:24 PM.

  6. #6
    Join Date
    Aug 2010
    Posts
    29

    Default

    Quote Originally Posted by JasonHogg View Post
    Three valued logic dictates that null can never equal null.
    That is, a null size will never equal a null size so you database lookup won't work when the size is null.
    Solutions:
    1) Make the database default the size to zero when it is null and do the same conversion in your data stream; or
    2) Put in an intermediate view and use coalesce to change nulls to zero (or some other value) and do the same in your data stream; or
    3) Use a database join or a merge join to gain more control over how nulls are used in the join.
    4) I've also used a concatenated business key in data warehousing that, in your case would push name, price and size together into a single key (separated with '|' or ';'). This simplifies the dimension lookup pattern by always matching on a single key and there are never any nulls to worry about. To do this, add a column to the table or add this business key to an intermediate view.

    Thank you JasonHogg and gutlez I'll try with your suggestions.

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.