Hitachi Vantara Pentaho Community Forums
Results 1 to 2 of 2

Thread: Potential Combi Lookup statement

  1. #1
    DEinspanjer Guest

    Default Potential Combi Lookup statement

    If one is using JDBC (which I have to imagine is very much the typical
    case), is there any reason to always craft the lookup statement with
    the funky NULL checks rather than just using
    Connection.getMetaData().getColumns(...) to check whether the
    particular lookup fields allow nulls or not?

    Especially with DB2 and Vertica not being able to support ? IS NULL
    without a cast, it seems like it might be worth looking into.

    Daniel
    --~--~---------~--~----~------------~-------~--~----~
    You received this message because you are subscribed to the Google Groups "kettle-developers" group.
    To post to this group, send email to kettle-developers (AT) googlegroups (DOT) com
    To unsubscribe from this group, send email to kettle-developers-unsubscribe (AT) g...oups (DOT) com
    For more options, visit this group at http://groups.google.com/group/kettle-developers?hl=en
    -~----------~----~----~----~------~----~------~--~---

  2. #2
    Matt Casters Guest

    Default Re: Potential Combi Lookup statement

    Hi Daniel,

    The main problem is that if you do "null = null" in SQL you get false back.
    As such, the only way to guarantee that you return a row, even if there are
    null values in the lookup data is to use the funky construct.

    Suppose we store 3 columns in Junk dimension D_JUNK: A, B, C
    As long as we store data in there that is not null, we're fine, we don't need
    to IS NULL in the SQL. However, if any of the columns can be null, we have
    to do this, otherwise we would constantly insert duplicate lines.

    The idea of checking the target database to see if a certain column can be
    null or not is a good one, but unfortunately not one I found reliable in the
    past. Perhaps it would be wiser to allow a flag per field to indicate if the
    field is "nullable?" or not. That way I would feel a lot safer about the
    whole thing. The same comment are valid for "dimension lookup/update" I
    suppose.

    All the best,

    Matt
    ____________________________________________
    Matt Casters
    Chief Data Integration - Kettle founder
    Pentaho, Open Source Business Intelligence
    http://www.pentaho.org -- mcasters (AT) pentaho (DOT) org
    Tel. +32 (0) 486 97 29 37


    On Saturday 12 April 2008 02:51:27 DEinspanjer wrote:
    > If one is using JDBC (which I have to imagine is very much the typical
    > case), is there any reason to always craft the lookup statement with
    > the funky NULL checks rather than just using
    > Connection.getMetaData().getColumns(...) to check whether the
    > particular lookup fields allow nulls or not?
    >
    > Especially with DB2 and Vertica not being able to support ? IS NULL
    > without a cast, it seems like it might be worth looking into.
    >
    > Daniel
    >


    --~--~---------~--~----~------------~-------~--~----~
    You received this message because you are subscribed to the Google Groups "kettle-developers" group.
    To post to this group, send email to kettle-developers (AT) googlegroups (DOT) com
    To unsubscribe from this group, send email to kettle-developers-unsubscribe (AT) g...oups (DOT) com
    For more options, visit this group at http://groups.google.com/group/kettle-developers?hl=en
    -~----------~----~----~----~------~----~------~--~---

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.