February 3, 2011 at 9:12 am
I have use Lookup transformation under SQL server 2005 with Cache enable option , When i m trying to null values with reference tables null value then it should be result match if both have NULL BUT in my case null match treated as not match and rows are redirected as error output.Can anyone help me out to resolve this issue ???
February 4, 2011 at 1:37 am
It is important to realise that apart from a few special cases (such as in a group by) NULL is never considered equal to anything (including another null).
One way round this may be to use the coalesce function to replace the nulls with a known value(something like -1 may be good if it cannot occur in the data and the column is numeric) and then compare the result.
Mike
February 9, 2011 at 3:44 am
Instead of using Lookup task, you can use conditional split and give the condition as isnull(columnname) or Column is null.so that you can able to insert the null values to the destination.
February 9, 2011 at 7:02 am
To further explain what Mike was saying:
In computing, NULL means unknown. Not nothing. Not zero. It means "I don't know the value of this variable/object." Since the value is not known, there's no way to compare NULL to NULL. That's like saying, "We need to compare some object we've never seen before to a random piece of something that we don't know anything about. But they should be equal, right?"
If you absolutely must match nullable columns to each other, the quick and dirty way is to do an ISNULL() comparison. I.E. ISNULL(MyColumn1,0) = ISNULL(MyColumn2,0). If column 1 is NULL, it gets set to a zero and compares to the value in column 2. If column 2 is NULL, vice-versa. And if both columns are NULL, they match only because you told the code to pretend NULLs were zeroes.
Does that make sense?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply