Potential Combi Lookup statement

[DEinspanjer]

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) googlegroups (DOT) com
For more options, visit this group at http://groups.google.com/group/kettle-developers?hl=en
-~----------~----~----~----~------~----~------~--~---

[Matt Casters]

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) googlegroups (DOT) com
For more options, visit this group at http://groups.google.com/group/kettle-developers?hl=en
-~----------~----~----~----~------~----~------~--~---