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

From: Christopher G Tantalo (ctantalo_at_paychex.com)
Date: 04/30/04 

Date: Fri, 30 Apr 2004 09:48:26 -0400
To: Chuck Fox <chuckfox2@aol.com>

Chuck Fox wrote:

> mpeppler@peppler.org 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.

Relevant Pages

  • Re: Solaris 5.8 - need way to collect source host information
    ... Thanks Michael - of course!! ... using a common login. ... is my last login if this command is run by me. ... while -F ' ' forces it to treat Tabs as normal characters. ...
    (comp.unix.shell)
  • Re: Matlab crashes
    ... Michael Wild wrote in ... When I launch the program from command window, ... Operating System: Microsoft Windows XP ... The only command that I use is "write", ...
    (comp.soft-sys.matlab)
  • Re: winzip in macro
    ... > which lets you enter command switches with the Basic Winzip command. ... > The one "gotcha" I never got past was that unless the user is using Outlook, ... One of these days I'm going to write a macro to use ... > "Michael" wrote in message ...
    (microsoft.public.excel.programming)
  • Re: Win32_Process CommandLine returning Null
    ... Thanks for the reply Michael. ... properties come back as Null if the process is running under an account ... I noticed that the MSFT iisapp.vbs script asserts the '' privilege ... the following command correctly shows me the w3wp ...
    (microsoft.public.win32.programmer.wmi)
  • Re: Selecting a record froma table where a column might be null
    ... On Fri, 2004-04-30 at 20:37, Chuck Fox wrote: ... >>that does not directly refer to a column, ... Michael ... Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short ...
    (perl.dbi.users)