Re: Selecting a record froma table where a column might be null

[Christopher G Tantalo]

Chuck Fox wrote:

[EMAIL PROTECTED] wrote:

On Thu, 2004-04-29 at 23:20, Chuck Fox wrote:
 

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
 

Michael and others

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

This worked perfectly.  Thank you very much.  I couldnt think of a way 
to do this with perl and the bind command, but this made it work.

--
-------------------------------
Just Your Friendly Neighborhood
_SPIDEY_


-----------------------------------------
The information contained in this message may be privileged, confidential, and protected from disclosure. If the reader of this message is not the intended recipient, or any employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by replying to the message and deleting it from your computer. 

Thank you. Paychex, Inc.