On Thu, 2004-04-29 at 23:20, Chuck Fox wrote:Michael and others
Hmmm,
So column != column when the column contains a null. How very unusual. I would have assumed that null = null, but there I go assuming things again.
So why does this work ?
create table foo( i int, c char(10) null ) go insert foo values( 1, null ) insert foo values( 2, "2" ) go
select * from foo where c = null
go
declare @c char(10)
select @c = null
select * from foo where c = isnull( @c, c )
go
If you are using Sybase then the default behavior is to allow comparison to NULL. This is NOT ANSI SQL compliant, and one should use "where foo is NULL" instead of "where foo = NULL" to be on the safe side.
This behavior is controlled by the ANSINULL option - see the SET T-SQL command.
Michael
Thanks for the lesson in non-compliant behavior in one's favorite product. I commonly use this trick to solve the exact problem the original questioner posted and assumed the technique would be as universal as duct tape. Sadly, this is not the case. However, another poster, Alan, did resolve the issue in the Oracle fashion with a technique that was quite similar in nature.
Try to add nvl function to both side of equal in the where clause.
eg. where nvl(column, '~') = nvl(?, '~')
Bad case of the heart was in the right place, but the head was in the wrong db.
Your Friendly Neighborhood DBA,
Chuck
