Can't get "<>" (not equals) to work in view

[emanning]

I'm having problems using "<>" as criteria for a varchar column in a
view. The data in this column is '2008-2009' which is supposed to
mean year 2008 thru 2009. If I set the criteria to "= '2008-2009'"
then the view returns just those rows with that field equal to
"2008-2009", like I would expect it to. But if I change the criteria
to "<'2008-2009'", the view returns nothing. And there are rows to
return. I tried CAST and CONVERT, thinking the view was interpreting
the criteria as numbers, but that didn't help. I tried both
CAST('2008-2009' AS varchar(9)) and CONVERT(varchar(9), '2008-2009').
Both would work with "=" but not with "<>".

I tried using "<>" on another column in the table and it worked fine.
This was a "last name" column so there weren't any numbers.

Any help or advice appreciated.

[Hugo Kornelis]

On Wed, 29 Oct 2008 14:49:05 -0700 (PDT), em******@kumc.edu wrote:

>I'm having problems using "<>" as criteria for a varchar column in a
view. The data in this column is '2008-2009' which is supposed to
mean year 2008 thru 2009. If I set the criteria to "= '2008-2009'"
then the view returns just those rows with that field equal to
"2008-2009", like I would expect it to. But if I change the criteria
to "<'2008-2009'", the view returns nothing. And there are rows to
return. I tried CAST and CONVERT, thinking the view was interpreting
the criteria as numbers, but that didn't help. I tried both
CAST('2008-2009' AS varchar(9)) and CONVERT(varchar(9), '2008-2009').
Both would work with "=" but not with "<>".

I tried using "<>" on another column in the table and it worked fine.
This was a "last name" column so there weren't any numbers.

Any help or advice appreciated.

Hi emanning,

Please post table structure (as a CREATE TABLE statement), some sample
data (as INSERT statements), the definition of your view (as a CREATE
VIEW statement), the query you are using to test, the data you got
returned and the data you expected to get.

Based on your description I can only conclude that either you overlooked
some seemingly minor detail that affects your results, or you ran into a
bug - and I consider the former to be more probable than the latter.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Erland Sommarskog]

(em******@kumc.edu) writes:

I'm having problems using "<>" as criteria for a varchar column in a
view. The data in this column is '2008-2009' which is supposed to
mean year 2008 thru 2009. If I set the criteria to "= '2008-2009'"
then the view returns just those rows with that field equal to
"2008-2009", like I would expect it to. But if I change the criteria
to "<'2008-2009'", the view returns nothing. And there are rows to
return. I tried CAST and CONVERT, thinking the view was interpreting
the criteria as numbers, but that didn't help. I tried both
CAST('2008-2009' AS varchar(9)) and CONVERT(varchar(9), '2008-2009').
Both would work with "=" but not with "<>".

Keep in mind that if there are rows with NULL in this column, they will
not be returned with <>, because NULL represents an unknown value, so we
don't know whether it unequal to '2008-2009'.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

[emanning]

On Oct 29, 5:43*pm, Erland Sommarskog <[e-mail]:

*([e-mail]) writes:

I'm having problems using "<>" as criteria for a varchar column in a
view. *The data in this column is '2008-2009' which is supposed to
mean year 2008 thru 2009. *If I set the criteria to "= '2008-2009'"
then the view returns just those rows with that field equal to
"2008-2009", like I would expect it to. *But if I change the criteria
to "<'2008-2009'", the view returns nothing. *And there are rows to
return. *I tried CAST and CONVERT, thinking the view was interpreting
the criteria as numbers, but that didn't help. *I tried both
CAST('2008-2009' AS varchar(9)) and CONVERT(varchar(9), '2008-2009').
Both would work with "=" but not with "<>".

Keep in mind that if there are rows with NULL in this column, they will
not be returned with <>, because NULL represents an unknown value, so we
don't know whether it unequal to '2008-2009'.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinf...ons/books.mspx

By adding "OR <column nameIS NULL" to the criteria statement solved
the problem. Thanks for your help.

[Andy M]

On Oct 30, 9:37*am, [e-mail] wrote:

By adding "OR <column nameIS NULL" to the criteria statement solved
the problem. *Thanks for your help.

Hi emanning,

You might try using this:
AND COALESCE(col_name,'') <'2008-2009'

Instead of this:
AND (col_name <'2008-2009 OR col_name IS NULL)

Depending on the complexity of the view definition, and the size of
the base table, you may find that COALESCE offers better performance
than using an OR...IS NULL