By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,843 Members | 2,300 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,843 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Oct 29 '08 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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
Oct 29 '08 #2

P: n/a
(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

Oct 29 '08 #3

P: n/a
On Oct 29, 5:43*pm, Erland Sommarskog <esq...@sommarskog.sewrote:
*(emann...@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, esq...@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
By adding "OR <column nameIS NULL" to the criteria statement solved
the problem. Thanks for your help.
Oct 30 '08 #4

P: n/a
On Oct 30, 9:37*am, emann...@kumc.edu 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
Oct 30 '08 #5

This discussion thread is closed

Replies have been disabled for this discussion.