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

[Chuck Fox]

[EMAIL PROTECTED] wrote:

On Fri, 2004-04-30 at 15:01, Chuck Fox wrote:
 

[EMAIL PROTECTED] wrote:

   

 

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.
   

:-)

Note that Sybase (via DBD::Sybase) will NOT accept a placeholder (?)
that does not directly refer to a column, so using something like
   where isnull(?, '~') ...
won't work. Similarly the code snippet mentioned in the DBI docs to
handle NULL parameters
   where foo = ? or (? is null and foo is null)
will NOT work, because Sybase's API can't figure out what type the
second ? refers to.
Ah the fun of writing portable SQL... :-)

Michael
 

Personally, I prefer stored procedures, but I can understand how that 
portability thing can get in the way :-)

Chuck