| Author |
Topic |
|
dlorenc
Posting Yak Master
172 Posts |
 Posted - 2008-10-10 : 14:37:58
|
| what's wrong with my where logic?I want to select comments where the commenttype is not 'Confidential' or 'Tickle'. commenttype is (nvarchar(20),null)...and charter 197 has several records with commenttypes = NULL....???the resulting set is empty...here is the query:SELECT * FROM CharterComments WHERE CharterComments.Charter_ID = 197 and (CharterComments.CommentType <> 'CONFIDENTIAL' or CharterComments.CommentType <> 'Tickle') |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2008-10-10 : 14:41:57
|
the 'and' between confidential and tickle still return an empty set...I was thinking something weird with nvarchar and nulls?... infact..if I simplify the where, I still dont understand why the set is empty...if I remove the 'and' I get about 10 records....all with commenttype = NULL...WHERE CharterComments.Charter_ID = 197 and CharterComments.CommentType <> 'CONFIDENTIAL' |
 |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2008-10-10 : 15:15:54
|
| I ran an UPDATE CharterComments SET CommentType = 'OPEN' where CommentType is Nulland the where works...go figgure..*sigh*...luckily this is a tiny database...this makes absolutely no sense to me...*sigh*...maybe I discovered a new sql rule?.."If nvarchar type field is compared with text values, nulls are discarded as they are, by definition, not having a text value."...rotf...As I tell my kids - "This is a mystery of life we may never understand..." |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2008-10-10 : 16:40:18
|
| mm...so 'nulls' would have to be explicitly included?..and commenttype is null...?? |
|
|
|
hanbingl
Aged Yak Warrior
652 Posts |
Posted - 2008-10-10 : 17:00:30
|
| [code]SELECT *FROM CharterCommentsWHERE CharterComments.Charter_ID = 197 and isnull(CharterComments.CommentType ,'') not in ('CONFIDENTIAL' ,'Tickle')[/code] |
|
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2008-10-10 : 17:20:02
|
again, thank you all for your help...I just never thought a null value had to be explicitly included in the query....go figgure... |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dlorenc
Posting Yak Master
172 Posts |
Posted - 2008-10-10 : 17:36:52
|
| Beginning SQL Server 2005 for Developers By Robin DewsonA NULL value means there is absolutely nothing entered in that column - no data at all. A column with a NULL value is a special data state, with special meaning. If a field has a NULL value, no data has been inserted into the column. this also means that you have to perform special function statements within any T-Sql code to test for this value. Take the example of a column defined to hold characters, but where one of the rows has a NULL value within it. If you completed a SQL function that carried out string manipulation, then the row with the NULL value would cause the row not to be included in the function without special processing. So what advantages are there to allowing NULL values? The largest advantage is that you know nothing has been entered in it...another advantage is teh small space that a NULL column takes up. To be precise, it takes up no space whatsoever, again, unlike a 0 or a single space.************now I understand the whole concept of 'NULL' much better...thank you all! |
|
|
|
|
'where' problem...