You could set the null values to something outside of your where statement as well using the isNull Function. SELECT * from MyTable where (NOT (isNull(MyCol,0)=-1)) -- /* Warren Brunk - MCITP - SQL 2005, MCDBA www.techintsolutions.com */ [quoted text, click to view] "Anders Eriksson" <andis59@gmail.com> wrote in message
news:8aiu9ub3k1zb$.dlg@ostling.com...
> Hello,
>
> I want to make an SQL query that selects all records in a table where a
> certain column is NOT -1
>
> The column is an int but may have the value NULL
>
> I have used this SQL statement:
>
> SELECT * from MyTable where (NOT (MyCol=-1))
>
> If I have an integer value in the column then it works, but it will fail
> on
> all where the column value is NULL.
>
> Why isn't NULL != MyCol=-1
>
> What SQL should I use to make this work?
>
> // Anders
> --
> English is not my first, or second, language
> so anything strange, or insulting, is due to
> the translation.
> Please correct me so I may learn better English!
Try SELECT * from MyTable where MyCol<>-1 or MyCol is null -- Hilary Cotter Director of Text Mining and Database Strategy RelevantNOISE.Com - Dedicated to mining blogs for business intelligence. This posting is my own and doesn't necessarily represent RelevantNoise's positions, strategies or opinions. Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Anders Eriksson" <andis59@gmail.com> wrote in message
news:8aiu9ub3k1zb$.dlg@ostling.com...
> Hello,
>
> I want to make an SQL query that selects all records in a table where a
> certain column is NOT -1
>
> The column is an int but may have the value NULL
>
> I have used this SQL statement:
>
> SELECT * from MyTable where (NOT (MyCol=-1))
>
> If I have an integer value in the column then it works, but it will fail
> on
> all where the column value is NULL.
>
> Why isn't NULL != MyCol=-1
>
> What SQL should I use to make this work?
>
> // Anders
> --
> English is not my first, or second, language
> so anything strange, or insulting, is due to
> the translation.
> Please correct me so I may learn better English!
Hello,
I want to make an SQL query that selects all records in a table where a
certain column is NOT -1
The column is an int but may have the value NULL
I have used this SQL statement:
SELECT * from MyTable where (NOT (MyCol=-1))
If I have an integer value in the column then it works, but it will fail on
all where the column value is NULL.
Why isn't NULL != MyCol=-1
What SQL should I use to make this work?
// Anders
--
English is not my first, or second, language
so anything strange, or insulting, is due to
the translation.
[quoted text, click to view] Anders Eriksson wrote:
> Hello,
>
> I want to make an SQL query that selects all records in a table where a
> certain column is NOT -1
>
> The column is an int but may have the value NULL
>
> I have used this SQL statement:
>
> SELECT * from MyTable where (NOT (MyCol=-1))
>
> If I have an integer value in the column then it works, but it will fail on
> all where the column value is NULL.
>
> Why isn't NULL != MyCol=-1
>
> What SQL should I use to make this work?
>
> // Anders
SQL Server uses three value logic (like all other SQL databases): true, false and NULL For NOT, the this means that NOT true is false NOT false is true NOT NULL is NULL. In your case, NULL = -1 is NULL, and NOT NULL is NULL. For more information, read some database book, or check http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls_printversion.asp -- Arto Viitanen, CSC Ltd.
[quoted text, click to view] On Wed, 11 Oct 2006 09:43:39 +0300, Arto Viitanen wrote:
(snip) [quoted text, click to view] >SQL Server uses three value logic (like all other SQL databases):
>
>true, false and NULL
Hi Arto, One minor correction - the third value in the logic tables used by SQL Server (and other SQL databases) is "Unknown", not NULL. This is a relevant difference, not just nitpicking words, because there are some rules for NULL that don't apply to the logic value Unknown. --
[quoted text, click to view] Hugo Kornelis wrote:
> On Wed, 11 Oct 2006 09:43:39 +0300, Arto Viitanen wrote:
>
> (snip)
>> SQL Server uses three value logic (like all other SQL databases):
>>
>> true, false and NULL
>
> Hi Arto,
>
> One minor correction - the third value in the logic tables used by SQL
> Server (and other SQL databases) is "Unknown", not NULL.
>
> This is a relevant difference, not just nitpicking words, because there
> are some rules for NULL that don't apply to the logic value Unknown.
>
Yes, you are right. I have read a master's thesis which describes different ways to interprete NULL. Unknown is only one of them (but most common one). -- Arto Viitanen, CSC Ltd.
[quoted text, click to view] On Wed, 11 Oct 2006 09:43:39 +0300, Arto Viitanen wrote: > SQL Server uses three value logic (like all other SQL databases): > > true, false and NULL > > For NOT, the this means that > > NOT true is false > NOT false is true > NOT NULL is NULL. > > In your case, NULL = -1 is NULL, and NOT NULL is NULL. > > For more information, read some database book, or check > > http://www.sqlservercentral.com/columnists/mcoles/fourrulesfornulls_printversion.asp This was a bit of a surprise! Can't really figure out why they decided on this. Thank you for your answer! // Anders // Anders -- English is not my first, or second, language so anything strange, or insulting, is due to the translation.
Don't see what you're looking for? Try a search.
|
