Including NULL Option in WHERE Clause

Hi,
I'm trying to create predicates that will work the same way whether a field
contains a blank or a null value. This is based on the fact that nulls get
converted to blanks when loaded into a VS control, then loaded into the
e.Values or e.OldValues arrays when deleting or updating the current row,
respectively, in a VS/C# FormView.
At the point where the query is constructed, I cannot tell whether the
original value was a null or not, so I attempted to use a CASE WHEN statement
in my WHERE clause like the following:
... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
ELSE '' END) ...
However, this doesn't work because the when [Address] is null, the CASE
statement returns a null which results in the predicate containing:
... AND (LTRIM(RTRIM([Address])) = null) ...
and that is not the same as:
... AND (LTRIM(RTRIM([Address])) IS null) ...
the first returns FALSE event though [Address] is null, while the second
returns TRUE.
I would further like to be able to construct this solution or any other that
works in a generic method that can be called by any query to construct its
predicate for each field where this situation is a possibility.
ThanksI think you're looking for the ISNULL function. It would be used like this:
AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
"WJB" wrote:
> Hi,
> I'm trying to create predicates that will work the same way whether a field
> contains a blank or a null value. This is based on the fact that nulls get
> converted to blanks when loaded into a VS control, then loaded into the
> e.Values or e.OldValues arrays when deleting or updating the current row,
> respectively, in a VS/C# FormView.
> At the point where the query is constructed, I cannot tell whether the
> original value was a null or not, so I attempted to use a CASE WHEN statement
> in my WHERE clause like the following:
> ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
> ELSE '' END) ...
> However, this doesn't work because the when [Address] is null, the CASE
> statement returns a null which results in the predicate containing:
> ... AND (LTRIM(RTRIM([Address])) = null) ...
> and that is not the same as:
> ... AND (LTRIM(RTRIM([Address])) IS null) ...
> the first returns FALSE event though [Address] is null, while the second
> returns TRUE.
> I would further like to be able to construct this solution or any other that
> works in a generic method that can be called by any query to construct its
> predicate for each field where this situation is a possibility.
> Thanks|||Not exactly. I had tried that already. The problem is that the field itself
actually could contain either null or blank. The ISNULL function as below
works if the field is blank but not if it is null. I need a solution that
works for both. Essentially, it needs to be a test that uses one value if
true and another if false. The problem is that "= null" in SQL Server is not
the same as "is null". If the ISNULL function had an overload that would
return a value if false and would equate to "IS NULL" if true, that would
work.
Can you suggest any other options?
Thanks
"Code Wench" wrote:
> I think you're looking for the ISNULL function. It would be used like this:
> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> "WJB" wrote:
> > Hi,
> >
> > I'm trying to create predicates that will work the same way whether a field
> > contains a blank or a null value. This is based on the fact that nulls get
> > converted to blanks when loaded into a VS control, then loaded into the
> > e.Values or e.OldValues arrays when deleting or updating the current row,
> > respectively, in a VS/C# FormView.
> >
> > At the point where the query is constructed, I cannot tell whether the
> > original value was a null or not, so I attempted to use a CASE WHEN statement
> > in my WHERE clause like the following:
> >
> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN null
> > ELSE '' END) ...
> >
> > However, this doesn't work because the when [Address] is null, the CASE
> > statement returns a null which results in the predicate containing:
> >
> > ... AND (LTRIM(RTRIM([Address])) = null) ...
> >
> > and that is not the same as:
> >
> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
> >
> > the first returns FALSE event though [Address] is null, while the second
> > returns TRUE.
> >
> > I would further like to be able to construct this solution or any other that
> > works in a generic method that can be called by any query to construct its
> > predicate for each field where this situation is a possibility.
> >
> > Thanks|||Well, did you try:
LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
"WJB" <WJB@.discussions.microsoft.com> wrote in message
news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
> Not exactly. I had tried that already. The problem is that the field
> itself
> actually could contain either null or blank. The ISNULL function as below
> works if the field is blank but not if it is null. I need a solution that
> works for both. Essentially, it needs to be a test that uses one value if
> true and another if false. The problem is that "= null" in SQL Server is
> not
> the same as "is null". If the ISNULL function had an overload that would
> return a value if false and would equate to "IS NULL" if true, that would
> work.
> Can you suggest any other options?
> Thanks
> "Code Wench" wrote:
>> I think you're looking for the ISNULL function. It would be used like
>> this:
>> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
>> "WJB" wrote:
>> > Hi,
>> >
>> > I'm trying to create predicates that will work the same way whether a
>> > field
>> > contains a blank or a null value. This is based on the fact that nulls
>> > get
>> > converted to blanks when loaded into a VS control, then loaded into the
>> > e.Values or e.OldValues arrays when deleting or updating the current
>> > row,
>> > respectively, in a VS/C# FormView.
>> >
>> > At the point where the query is constructed, I cannot tell whether the
>> > original value was a null or not, so I attempted to use a CASE WHEN
>> > statement
>> > in my WHERE clause like the following:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
>> > null
>> > ELSE '' END) ...
>> >
>> > However, this doesn't work because the when [Address] is null, the CASE
>> > statement returns a null which results in the predicate containing:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) = null) ...
>> >
>> > and that is not the same as:
>> >
>> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
>> >
>> > the first returns FALSE event though [Address] is null, while the
>> > second
>> > returns TRUE.
>> >
>> > I would further like to be able to construct this solution or any other
>> > that
>> > works in a generic method that can be called by any query to construct
>> > its
>> > predicate for each field where this situation is a possibility.
>> >
>> > Thanks|||Thanks, Aaron & Code Wench. Almost there. I think what I actually need is a
combination of the two answers, i.e.
LTRIM(RTRIM(ISNULL([Address], '')) = '' (in reality @.Address)
Since the e.Values and e.OldValues arrays are loaded with blanks by the
SQLDataSource/ObjectDataSource objects, this ISNULL in this case converts the
current value of null to a blank and so CompareAllValues works whether the
current value is blank or null. If it is not null, however, then ISNULL
returns [Address] and if that was also the original value, or the original
value of Address if not.
Thanks again for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:
> Well, did you try:
>
> LTRIM(RTRIM(ISNULL([Address], '')) = ISNULL([Address], '')
>
> "WJB" <WJB@.discussions.microsoft.com> wrote in message
> news:7442E8EC-50E2-4557-8425-3D13E512DEBC@.microsoft.com...
> > Not exactly. I had tried that already. The problem is that the field
> > itself
> > actually could contain either null or blank. The ISNULL function as below
> > works if the field is blank but not if it is null. I need a solution that
> > works for both. Essentially, it needs to be a test that uses one value if
> > true and another if false. The problem is that "= null" in SQL Server is
> > not
> > the same as "is null". If the ISNULL function had an overload that would
> > return a value if false and would equate to "IS NULL" if true, that would
> > work.
> >
> > Can you suggest any other options?
> >
> > Thanks
> >
> > "Code Wench" wrote:
> >
> >> I think you're looking for the ISNULL function. It would be used like
> >> this:
> >>
> >> AND (LTRIM(RTRIM([Address])) = ISNULL([Address], '')
> >>
> >> "WJB" wrote:
> >>
> >> > Hi,
> >> >
> >> > I'm trying to create predicates that will work the same way whether a
> >> > field
> >> > contains a blank or a null value. This is based on the fact that nulls
> >> > get
> >> > converted to blanks when loaded into a VS control, then loaded into the
> >> > e.Values or e.OldValues arrays when deleting or updating the current
> >> > row,
> >> > respectively, in a VS/C# FormView.
> >> >
> >> > At the point where the query is constructed, I cannot tell whether the
> >> > original value was a null or not, so I attempted to use a CASE WHEN
> >> > statement
> >> > in my WHERE clause like the following:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) = CASE WHEN [Address] is null THEN
> >> > null
> >> > ELSE '' END) ...
> >> >
> >> > However, this doesn't work because the when [Address] is null, the CASE
> >> > statement returns a null which results in the predicate containing:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) = null) ...
> >> >
> >> > and that is not the same as:
> >> >
> >> > ... AND (LTRIM(RTRIM([Address])) IS null) ...
> >> >
> >> > the first returns FALSE event though [Address] is null, while the
> >> > second
> >> > returns TRUE.
> >> >
> >> > I would further like to be able to construct this solution or any other
> >> > that
> >> > works in a generic method that can be called by any query to construct
> >> > its
> >> > predicate for each field where this situation is a possibility.
> >> >
> >> > Thanks
>
>