December 11, 2007 at 5:47 pm
I have some ETL scripts I've written which check about 50 attributes for differences between live data and my data warehouse data. Data on either side may be NULL at some point, and as far as I'm concerned it means the data is no longer equal. Yes, I understand that data should be used in place of NULL (even if it's some kind of representative code, like a 0 or -1 where numbers are normally positive) but I have no control over the data in the live system.
So, my scripts compare live and DW fields using the <> operator. I've noticed that NULL values are not considered unequal to known values, and I understand this logic. I could wrap every one of my fields in an ISNULL construct and use some arbitrary value which should never appear in live data. This is a bit tedious. Is there a SET flag I can use at the start of my query to tell SQL Server to explicitly handle NULLs as being different to known values?
December 11, 2007 at 6:40 pm
This is NOT a recommended, but...you can use the SET ANSI_NULLS OFF notation. It should allow you to use it that way.
That being said - you really should pursue using ISNULL() or simply adding in logic using IS NULL or IS NOT NULL. More painful , since you would have to recodeom some things - but the ANSI_NULLS thing is not compliant, and really in my mind should be avoided (although this is precisely why it's here...).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
December 11, 2007 at 7:42 pm
Fantastic, I'll give that a fly. Thanks Matt.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply