Asp Forum - Problems with Coalesce - nullif - microsoft.public.sqlserver.programming

SteveInBeloit

3/15/2007 3:11:00 PM

Hi,
I have a Select statement that uses Coalesce with Nullif in the Where
clause. I thought all has been working good. I found a row that it is not
returning and have it narrowed down to this line in the Where:

AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)

I pass @Color in as NULL. odColor contains NULL in the table. If I comment
this line out, I get the row, if I uncomment it, I do not get it.

What am I not seeing?
Thanks,
Steve

8 Answers

Tom Cooper

3/15/2007 3:33:00 PM

First, since odColor contains NULL,

Where odColor LIKE ...

will never return true no matter what is on the right side of the LIKE
clause.

Second, I don't understand why you wrote the right side of your expression
the way you did. Since '%' + @Color + '%' cannot possibly be the empty
string '', NULLIF('%' + @Color + '%', '') will always give you the value of
'%' + @Color + '%', so the NULLIF function is not needed.

Please explain what you are trying to do and we can probably help you
rewrite this.

Tom

"SteveInBeloit" <SteveInBeloit@discussions.microsoft.com> wrote in message
news:937B6857-AA3E-454A-82C9-35E4EE487425@microsoft.com...
> Hi,
> I have a Select statement that uses Coalesce with Nullif in the Where
> clause. I thought all has been working good. I found a row that it is
> not
> returning and have it narrowed down to this line in the Where:
>
> AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
>
> I pass @Color in as NULL. odColor contains NULL in the table. If I
> comment
> this line out, I get the row, if I uncomment it, I do not get it.
>
> What am I not seeing?
> Thanks,
> Steve

Jim Underwood

3/15/2007 3:44:00 PM

Let's look at your code, substitute in the null values, and then perform the
functions to see what we would get.

AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
-- @Color is Null
AND odColor LIKE COALESCE (NULLIF('%' + Null + '%', ''), odColor)
-- concatenation on Null returns Null
AND odColor LIKE COALESCE (NULLIF(Null , ''), odColor)
-- NullIf sees a null, and returns the alternative, an empty string
AND odColor LIKE COALESCE ('', odColor)
-- Coalesce returns the first non-null value, in this case an empty string
AND odColor LIKE ''

I'm not sure what your intent is, as your description was quite vague.
Everything that follows is a guess on my part, because you did not include
DDL, sample data, and expected results.
for details see: http://www.aspfaq.com/etiquette.a...
If you post these, you will get a much better response based on what you
actually need. Make sure to include your full SQL query, or stored
procedure code, as I suspect you may be using an overcomplicated approach
that can be corrected more easily elsewhere in your code.

I think, as the code is written, odColor will never be returned by the
coalesce, because your coalesce will always return the value of your NULLIF.

If your intent is to return rows, regardless of the value in odColor, then
replace your coalesce with:
AND odColor LIKE '%' + NULLIF(@Color, '') + '%'
which will compare odColor to '%%' if @Color is null.

Or, remove the NULLIF and just use coalesce:
AND odColor LIKE COALESCE ('%' + @Color + '%', odColor)
which will compare odColor to itself if @Color is null.

If this is a stored proc, check for nulls earlier in the code and replace
@Color with the desired value prior to passing it into the SQL.

"SteveInBeloit" <SteveInBeloit@discussions.microsoft.com> wrote in message
news:937B6857-AA3E-454A-82C9-35E4EE487425@microsoft.com...
> Hi,
> I have a Select statement that uses Coalesce with Nullif in the Where
> clause. I thought all has been working good. I found a row that it is
not
> returning and have it narrowed down to this line in the Where:
>
> AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
>
> I pass @Color in as NULL. odColor contains NULL in the table. If I
comment
> this line out, I get the row, if I uncomment it, I do not get it.
>
> What am I not seeing?
> Thanks,
> Steve

SteveInBeloit

3/15/2007 4:33:00 PM

Sorry for the incompleteness, I am always leary of getting too long of a post.

The whole where claus is:

WHERE odRcvdDt >= @FromDate AND odRcvdDt <= @ToDate AND odShipCmplt = 1
AND cmCustName LIKE COALESCE (NULLIF (@Company + '%', ''), cmCustName)
AND ohPO LIKE COALESCE (NULLIF (@PO + '%', ''), ohPO)
AND odShopTktDieNo LIKE COALESCE (NULLIF(@Die + '%', ''), odShopTktDieNo)
AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
AND cpCustomerItem LIKE COALESCE (NULLIF(@PartNo, '')+'%', cpCustomerItem)

I pass in any combinations of these parameters, or null for the ones I do
not want to filter on.

For Color for example, if the color is spaces in the table, I get it back,
if it is NULL in the table, I get nothing. Maybe I am getting nothing when
other fields are NULL also and I just don't know it?

Thanks

"Tom Cooper" wrote:

> First, since odColor contains NULL,
>
> Where odColor LIKE ...
>
> will never return true no matter what is on the right side of the LIKE
> clause.
>
> Second, I don't understand why you wrote the right side of your expression
> the way you did. Since '%' + @Color + '%' cannot possibly be the empty
> string '', NULLIF('%' + @Color + '%', '') will always give you the value of
> '%' + @Color + '%', so the NULLIF function is not needed.
>
> Please explain what you are trying to do and we can probably help you
> rewrite this.
>
> Tom
>
> "SteveInBeloit" <SteveInBeloit@discussions.microsoft.com> wrote in message
> news:937B6857-AA3E-454A-82C9-35E4EE487425@microsoft.com...
> > Hi,
> > I have a Select statement that uses Coalesce with Nullif in the Where
> > clause. I thought all has been working good. I found a row that it is
> > not
> > returning and have it narrowed down to this line in the Where:
> >
> > AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
> >
> > I pass @Color in as NULL. odColor contains NULL in the table. If I
> > comment
> > this line out, I get the row, if I uncomment it, I do not get it.
> >
> > What am I not seeing?
> > Thanks,
> > Steve
>
>
>

SteveInBeloit

3/15/2007 4:44:00 PM

Jim, I posted the complete Where clause above. I tried both of your
suggestions, but still only get the ones where the color is spaces, I don't
get the ones where they are null.

Steve

"Jim Underwood" wrote:

> Let's look at your code, substitute in the null values, and then perform the
> functions to see what we would get.
>
> AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
> -- @Color is Null
> AND odColor LIKE COALESCE (NULLIF('%' + Null + '%', ''), odColor)
> -- concatenation on Null returns Null
> AND odColor LIKE COALESCE (NULLIF(Null , ''), odColor)
> -- NullIf sees a null, and returns the alternative, an empty string
> AND odColor LIKE COALESCE ('', odColor)
> -- Coalesce returns the first non-null value, in this case an empty string
> AND odColor LIKE ''
>
> I'm not sure what your intent is, as your description was quite vague.
> Everything that follows is a guess on my part, because you did not include
> DDL, sample data, and expected results.
> for details see: http://www.aspfaq.com/etiquette.a...
> If you post these, you will get a much better response based on what you
> actually need. Make sure to include your full SQL query, or stored
> procedure code, as I suspect you may be using an overcomplicated approach
> that can be corrected more easily elsewhere in your code.
>
> I think, as the code is written, odColor will never be returned by the
> coalesce, because your coalesce will always return the value of your NULLIF.
>
> If your intent is to return rows, regardless of the value in odColor, then
> replace your coalesce with:
> AND odColor LIKE '%' + NULLIF(@Color, '') + '%'
> which will compare odColor to '%%' if @Color is null.
>
> Or, remove the NULLIF and just use coalesce:
> AND odColor LIKE COALESCE ('%' + @Color + '%', odColor)
> which will compare odColor to itself if @Color is null.
>
> If this is a stored proc, check for nulls earlier in the code and replace
> @Color with the desired value prior to passing it into the SQL.
>
>
> "SteveInBeloit" <SteveInBeloit@discussions.microsoft.com> wrote in message
> news:937B6857-AA3E-454A-82C9-35E4EE487425@microsoft.com...
> > Hi,
> > I have a Select statement that uses Coalesce with Nullif in the Where
> > clause. I thought all has been working good. I found a row that it is
> not
> > returning and have it narrowed down to this line in the Where:
> >
> > AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
> >
> > I pass @Color in as NULL. odColor contains NULL in the table. If I
> comment
> > this line out, I get the row, if I uncomment it, I do not get it.
> >
> > What am I not seeing?
> > Thanks,
> > Steve
>
>
>

Jim Underwood

3/15/2007 5:27:00 PM

As Tom pointed out, you won't see any results when the column values are
null, because you are doing a
COLUMN like XYZ
if COLUMN is null, then any comparison will always return false, because
NULL can never be equal, not equal, or like any value, including NULL.

Try running the following code in QA and you will see what happens.

select 'TRUE' where 1=1

select 'TRUE' where NULL = NULL

select 'TRUE' where NULL = 1

select 'TRUE' where NULL like ''

select 'TRUE' where NULL Like NULL

declare @Val as varchar(10)
set @Val = NULL

select 'TRUE' where @Val = @Val

select 'TRUE' where @Val like @Val

"SteveInBeloit" <SteveInBeloit@discussions.microsoft.com> wrote in message
news:617FC2A9-0718-4E39-BC58-056C7B3A3035@microsoft.com...
> Jim, I posted the complete Where clause above. I tried both of your
> suggestions, but still only get the ones where the color is spaces, I
don't
> get the ones where they are null.
>
> Steve
>
> "Jim Underwood" wrote:
>
> > Let's look at your code, substitute in the null values, and then perform
the
> > functions to see what we would get.
> >
> > AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
> > -- @Color is Null
> > AND odColor LIKE COALESCE (NULLIF('%' + Null + '%', ''), odColor)
> > -- concatenation on Null returns Null
> > AND odColor LIKE COALESCE (NULLIF(Null , ''), odColor)
> > -- NullIf sees a null, and returns the alternative, an empty string
> > AND odColor LIKE COALESCE ('', odColor)
> > -- Coalesce returns the first non-null value, in this case an empty
string
> > AND odColor LIKE ''
> >
> > I'm not sure what your intent is, as your description was quite vague.
> > Everything that follows is a guess on my part, because you did not
include
> > DDL, sample data, and expected results.
> > for details see: http://www.aspfaq.com/etiquette.a...
> > If you post these, you will get a much better response based on what you
> > actually need. Make sure to include your full SQL query, or stored
> > procedure code, as I suspect you may be using an overcomplicated
approach
> > that can be corrected more easily elsewhere in your code.
> >
> > I think, as the code is written, odColor will never be returned by the
> > coalesce, because your coalesce will always return the value of your
NULLIF.
> >
> > If your intent is to return rows, regardless of the value in odColor,
then
> > replace your coalesce with:
> > AND odColor LIKE '%' + NULLIF(@Color, '') + '%'
> > which will compare odColor to '%%' if @Color is null.
> >
> > Or, remove the NULLIF and just use coalesce:
> > AND odColor LIKE COALESCE ('%' + @Color + '%', odColor)
> > which will compare odColor to itself if @Color is null.
> >
> > If this is a stored proc, check for nulls earlier in the code and
replace
> > @Color with the desired value prior to passing it into the SQL.
> >
> >
> > "SteveInBeloit" <SteveInBeloit@discussions.microsoft.com> wrote in
message
> > news:937B6857-AA3E-454A-82C9-35E4EE487425@microsoft.com...
> > > Hi,
> > > I have a Select statement that uses Coalesce with Nullif in the Where
> > > clause. I thought all has been working good. I found a row that it
is
> > not
> > > returning and have it narrowed down to this line in the Where:
> > >
> > > AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
> > >
> > > I pass @Color in as NULL. odColor contains NULL in the table. If I
> > comment
> > > this line out, I get the row, if I uncomment it, I do not get it.
> > >
> > > What am I not seeing?
> > > Thanks,
> > > Steve
> >
> >
> >

Tom Cooper

3/15/2007 5:28:00 PM

Sorry if I was unclear. The point I was trying to make was that a
comparison like
cmCustName LIKE COALESCE (NULLIF (@Company + '%', ''), cmCustName)
will not be true if cmCustName is NULL.

This is because when you compare NULL to any value (including NULL), it
returns "UNKNOWN", which is neither TRUE nor FALSE. (As an aside, there is
an option you can set that would change this, but using that option is not a
good practice.) This is because when you are working with values that might
be NULL, you are using something called 3 valued logic, a good description
of it can be found at
http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls_printv...

So, yes, it is true that you would have the same problem if ohPO was NULL
and @PO was NULL, or any of the other combinations.

Doing this type of dynamic search where the user can pass in several
different parameters, leaving the ones the user is not concerned with NULL,
is more complicated than it appears at first glance. In particular, even
when you get it working, you will often find that the performance is very
bad when the database is large. A good article to read to give you some
ideas on how to do this and make the result efficient can be found at
http://www.sommarskog.se/dyn-s...

Tom

"SteveInBeloit" <SteveInBeloit@discussions.microsoft.com> wrote in message
news:7EE126AE-1A5D-452A-9B2A-D67E865B398D@microsoft.com...
> Sorry for the incompleteness, I am always leary of getting too long of a
> post.
>
> The whole where claus is:
>
> WHERE odRcvdDt >= @FromDate AND odRcvdDt <= @ToDate AND odShipCmplt = 1
> AND cmCustName LIKE COALESCE (NULLIF (@Company + '%', ''), cmCustName)
> AND ohPO LIKE COALESCE (NULLIF (@PO + '%', ''), ohPO)
> AND odShopTktDieNo LIKE COALESCE (NULLIF(@Die + '%', ''), odShopTktDieNo)
> AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
> AND cpCustomerItem LIKE COALESCE (NULLIF(@PartNo, '')+'%', cpCustomerItem)
>
> I pass in any combinations of these parameters, or null for the ones I do
> not want to filter on.
>
> For Color for example, if the color is spaces in the table, I get it back,
> if it is NULL in the table, I get nothing. Maybe I am getting nothing
> when
> other fields are NULL also and I just don't know it?
>
> Thanks
>
>
> "Tom Cooper" wrote:
>
>> First, since odColor contains NULL,
>>
>> Where odColor LIKE ...
>>
>> will never return true no matter what is on the right side of the LIKE
>> clause.
>>
>> Second, I don't understand why you wrote the right side of your
>> expression
>> the way you did. Since '%' + @Color + '%' cannot possibly be the empty
>> string '', NULLIF('%' + @Color + '%', '') will always give you the value
>> of
>> '%' + @Color + '%', so the NULLIF function is not needed.
>>
>> Please explain what you are trying to do and we can probably help you
>> rewrite this.
>>
>> Tom
>>
>> "SteveInBeloit" <SteveInBeloit@discussions.microsoft.com> wrote in
>> message
>> news:937B6857-AA3E-454A-82C9-35E4EE487425@microsoft.com...
>> > Hi,
>> > I have a Select statement that uses Coalesce with Nullif in the Where
>> > clause. I thought all has been working good. I found a row that it is
>> > not
>> > returning and have it narrowed down to this line in the Where:
>> >
>> > AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
>> >
>> > I pass @Color in as NULL. odColor contains NULL in the table. If I
>> > comment
>> > this line out, I get the row, if I uncomment it, I do not get it.
>> >
>> > What am I not seeing?
>> > Thanks,
>> > Steve
>>
>>
>>

Jim Underwood

3/15/2007 5:36:00 PM

The COALESCE (NULLIF (@Company + '%', ''), cmCustName) will result in an
empty string when @Company is null, as will all the other COALESCE functions
that you use, because your NULLIF is always returning a value. You want to
use one or the other, not both.

However, this will correct your syntax for that function, but it still will
not work when the column value is null, for the reasons Tom and I explained.

You could use something like the following. It will work technically, but
the performance might be terrible.

WHERE odRcvdDt >= @FromDate
AND odRcvdDt <= @ToDate
AND odShipCmplt = 1
AND (cmCustName LIKE @Company + '%' or @Company is null)
AND (ohPO LIKE @PO + '%' or @PO is null)
AND (odShopTktDieNo LIKE @Die + '%' or @Die is null)
AND (odColor LIKE '%' + @Color + '%' or @Color is null)
AND (cpCustomerItem LIKE @PartNo or @PartNo is null)

You might try using dynamic SQL, which has it's own issues, but lets you
decide whether or not to include the parameters in the where clause.

Look here for information on using Dynamic SQL, paying particular attention
to the section on SQL injection:
http://www.sommarskog.se/dynami...
http://www.sommarskog.se/dyn-s...

"SteveInBeloit" <SteveInBeloit@discussions.microsoft.com> wrote in message
news:7EE126AE-1A5D-452A-9B2A-D67E865B398D@microsoft.com...
> Sorry for the incompleteness, I am always leary of getting too long of a
post.
>
> The whole where claus is:
>
> WHERE odRcvdDt >= @FromDate AND odRcvdDt <= @ToDate AND odShipCmplt = 1
> AND cmCustName LIKE COALESCE (NULLIF (@Company + '%', ''), cmCustName)
> AND ohPO LIKE COALESCE (NULLIF (@PO + '%', ''), ohPO)
> AND odShopTktDieNo LIKE COALESCE (NULLIF(@Die + '%', ''), odShopTktDieNo)
> AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
> AND cpCustomerItem LIKE COALESCE (NULLIF(@PartNo, '')+'%', cpCustomerItem)
>
> I pass in any combinations of these parameters, or null for the ones I do
> not want to filter on.
>
> For Color for example, if the color is spaces in the table, I get it back,
> if it is NULL in the table, I get nothing. Maybe I am getting nothing
when
> other fields are NULL also and I just don't know it?
>
> Thanks
>
>
> "Tom Cooper" wrote:
>
> > First, since odColor contains NULL,
> >
> > Where odColor LIKE ...
> >
> > will never return true no matter what is on the right side of the LIKE
> > clause.
> >
> > Second, I don't understand why you wrote the right side of your
expression
> > the way you did. Since '%' + @Color + '%' cannot possibly be the empty
> > string '', NULLIF('%' + @Color + '%', '') will always give you the value
of
> > '%' + @Color + '%', so the NULLIF function is not needed.
> >
> > Please explain what you are trying to do and we can probably help you
> > rewrite this.
> >
> > Tom
> >
> > "SteveInBeloit" <SteveInBeloit@discussions.microsoft.com> wrote in
message
> > news:937B6857-AA3E-454A-82C9-35E4EE487425@microsoft.com...
> > > Hi,
> > > I have a Select statement that uses Coalesce with Nullif in the Where
> > > clause. I thought all has been working good. I found a row that it
is
> > > not
> > > returning and have it narrowed down to this line in the Where:
> > >
> > > AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
> > >
> > > I pass @Color in as NULL. odColor contains NULL in the table. If I
> > > comment
> > > this line out, I get the row, if I uncomment it, I do not get it.
> > >
> > > What am I not seeing?
> > > Thanks,
> > > Steve
> >
> >
> >

SteveInBeloit

3/15/2007 6:19:00 PM

Jim/Tom,
Thank you both very much for helping me understand this. I can now see why
I get back rows where odcolor is spaces, but not null. Thanks also for the
reading references, I will need to read them and understand them better
before I know what to change to make this work.
Steve

"Jim Underwood" wrote:

> As Tom pointed out, you won't see any results when the column values are
> null, because you are doing a
> COLUMN like XYZ
> if COLUMN is null, then any comparison will always return false, because
> NULL can never be equal, not equal, or like any value, including NULL.
>
> y running the following code in QA and you will see what happens.
>
> select 'TRUE' where 1=1
>
> select 'TRUE' where NULL = NULL
>
> select 'TRUE' where NULL = 1
>
> select 'TRUE' where NULL like ''
>
> select 'TRUE' where NULL Like NULL
>
> declare @Val as varchar(10)
> set @Val = NULL
>
> select 'TRUE' where @Val = @Val
>
> select 'TRUE' where @Val like @Val
>
> "SteveInBeloit" <SteveInBeloit@discussions.microsoft.com> wrote in message
> news:617FC2A9-0718-4E39-BC58-056C7B3A3035@microsoft.com...
> > Jim, I posted the complete Where clause above. I tried both of your
> > suggestions, but still only get the ones where the color is spaces, I
> don't
> > get the ones where they are null.
> >
> > Steve
> >
> > "Jim Underwood" wrote:
> >
> > > Let's look at your code, substitute in the null values, and then perform
> the
> > > functions to see what we would get.
> > >
> > > AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
> > > -- @Color is Null
> > > AND odColor LIKE COALESCE (NULLIF('%' + Null + '%', ''), odColor)
> > > -- concatenation on Null returns Null
> > > AND odColor LIKE COALESCE (NULLIF(Null , ''), odColor)
> > > -- NullIf sees a null, and returns the alternative, an empty string
> > > AND odColor LIKE COALESCE ('', odColor)
> > > -- Coalesce returns the first non-null value, in this case an empty
> string
> > > AND odColor LIKE ''
> > >
> > > I'm not sure what your intent is, as your description was quite vague.
> > > Everything that follows is a guess on my part, because you did not
> include
> > > DDL, sample data, and expected results.
> > > for details see: http://www.aspfaq.com/etiquette.a...
> > > If you post these, you will get a much better response based on what you
> > > actually need. Make sure to include your full SQL query, or stored
> > > procedure code, as I suspect you may be using an overcomplicated
> approach
> > > that can be corrected more easily elsewhere in your code.
> > >
> > > I think, as the code is written, odColor will never be returned by the
> > > coalesce, because your coalesce will always return the value of your
> NULLIF.
> > >
> > > If your intent is to return rows, regardless of the value in odColor,
> then
> > > replace your coalesce with:
> > > AND odColor LIKE '%' + NULLIF(@Color, '') + '%'
> > > which will compare odColor to '%%' if @Color is null.
> > >
> > > Or, remove the NULLIF and just use coalesce:
> > > AND odColor LIKE COALESCE ('%' + @Color + '%', odColor)
> > > which will compare odColor to itself if @Color is null.
> > >
> > > If this is a stored proc, check for nulls earlier in the code and
> replace
> > > @Color with the desired value prior to passing it into the SQL.
> > >
> > >
> > > "SteveInBeloit" <SteveInBeloit@discussions.microsoft.com> wrote in
> message
> > > news:937B6857-AA3E-454A-82C9-35E4EE487425@microsoft.com...
> > > > Hi,
> > > > I have a Select statement that uses Coalesce with Nullif in the Where
> > > > clause. I thought all has been working good. I found a row that it
> is
> > > not
> > > > returning and have it narrowed down to this line in the Where:
> > > >
> > > > AND odColor LIKE COALESCE (NULLIF('%' + @Color + '%', ''), odColor)
> > > >
> > > > I pass @Color in as NULL. odColor contains NULL in the table. If I
> > > comment
> > > > this line out, I get the row, if I uncomment it, I do not get it.
> > > >
> > > > What am I not seeing?
> > > > Thanks,
> > > > Steve
> > >
> > >
> > >
>
>
>

microsoft.public.sqlserver.programming

Problems with Coalesce - nullif

SteveInBeloit

Tom Cooper

Jim Underwood

SteveInBeloit

SteveInBeloit

Jim Underwood

Tom Cooper

Jim Underwood

SteveInBeloit

x Login to ForumsZone