Archives of the TeradataForum

Message Posted: Mon, 24 Dec 2007 @ 11:07:47 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Bill,

I might be wrong in my understanding, but as far as I know, "NULL in SQL context" is not the same as "character null".

"NULL" in SQL context literally means "there is nothing/it is empty", whereas the null character in ANSI/ASCII context has a physical placeholder (i.e. there is "something"). An ASCII "null" can have a hex representation, whereas SQL "nothing" cannot have any character representation. You will therefore see that:

     select CHAR2HEXINT(cast(null as char(1)));
     ..... still returns null.

I agree with Michael Larkins that the only realistic known approach is to use coalesce. There is a caveat however: you need to be sure that your substitution value will not be present in the rest of your data (now or in the future). Otherwise your queries will be matching incorrectly, giving invalid results. Having a match where you shouldn't have had one can have some nasty side-effects.

For example:

Table t1 has following data for column c1:

     abc
     
     def
     #$@%

Table t2 has following data for column c1:

     
     def
     abc

     select t2.*
     from t1, t2
     where coalesce(t2.c1,'#$@%') = coalesce(t1.c1,'#$@%');

Result:

     abc
     
     
     def

--> Should only have matched to the <null> record once, but the substitution value chosen for <null> ('#$@%') was actually part of the data in one of the tables!!!

It is beyond me why Teradata (and other database vendors) cannot update their technology to handle null equality operations. To 100% work around the above reliability issue using an "OR" clause as in the following example creates TREMENDOUS PERFORMANCE PROBLEMS:

     Where (t1.c1 = t2.c1
             OR
             (t1.c1 is null
              and
              t2.c1 is null)
             )

Regards

Etienne Stieger
Analytics and Information Management (AIM/EIW)
Standard Bank of South Africa Ltd

	<Prev	Next>		<<First	<Prev	Next>	Last>>