NULL values in some of the rows. When I compare the two
columns, I don't get the rows that have NULL values (I am
comparing them to be NOT the same e.g Select * from
mytable where column1 <> column2). I get all the rows that
are not the same but have values but not the NULL columns.
How can I get the NULL columns too '
Thanks for help.IS NULL and IS NOT NULL are the predicates used to test for the presence or
absence of NULL:
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
You can read about three-value logic and NULL values in Books Online:
http://msdn.microsoft.com/library/e..._qd_02_8pwy.asp
In general a comparison of anything to NULL is always UNKNOWN which means
NULL values are excluded by statements like
...
WHERE x <> y
David Portas
--
Please reply only to the newsgroup
--|||ANSI defines NULL = NULL as false.
NULL is the abscence of a value and, NULL does not equal NULL.
You can:
a) use IS NULL to perform the test or
b) you could say SET ANSI_NULLS OFF
Books Online will have lot's more info on each option... I hope this helps,
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:000e01c3dc43$7c50b750$a101280a@.phx.gbl...
quote:|||> b) you could say SET ANSI_NULLS OFF
> I have two Smalldatetime columns. One of the columns have
> NULL values in some of the rows. When I compare the two
> columns, I don't get the rows that have NULL values (I am
> comparing them to be NOT the same e.g Select * from
> mytable where column1 <> column2). I get all the rows that
> are not the same but have values but not the NULL columns.
> How can I get the NULL columns too '
>
> Thanks for help.
ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE - it's still UNKNOWN.
David Portas
--
Please reply only to the newsgroup
--|||I guess I have something wrong in my query. I also was
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
quote:
>--Original Message--
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
quote:|||I guess I have something wrong in my query. I also was
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>
trying to exclude the values in one of the columns which
was the dates that had 1900 in it (E.g NOT Like '%1900%').
I was trying to add that statement to
SELECT *
FROM mytable
WHERE column1 <> column2
OR column1 IS NULL
OR column2 IS NULL
--OR/AND column1 Not like '%1900%'
but somehow it is eliminating the NULL columns.
Thanks.
quote:
>--Original Message--
>ANSI_NULLS OFF won't evaluate x<>NULL to TRUE or FALSE -
it's still UNKNOWN.
quote:|||SORRY FOR THE DOUBLE POST...
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>
quote:
>--Original Message--
>I guess I have something wrong in my query. I also was
>trying to exclude the values in one of the columns which
>was the dates that had 1900 in it (E.g NOT Like '%
1900%').
quote:|||It helps if you can post some code to reproduce your problem and show your
>I was trying to add that statement to
>SELECT *
> FROM mytable
> WHERE column1 <> column2
> OR column1 IS NULL
> OR column2 IS NULL
>--OR/AND column1 Not like '%1900%'
>but somehow it is eliminating the NULL columns.
>Thanks.
>
>it's still UNKNOWN.
>.
>
required results. It's not clear to me exactly what result you're trying to
get in this case.
When combining AND and OR you have to watch out for the order of precedence.
Use parentheses to make the order of evaluation clear otherwise x OR y AND z
is evaluated as x OR (y AND z).
Don't use LIKE to compare dates. LIKE performs a string comparison and if
you use it on dates then every date has to be cast as a string. Best way to
eliminate the year 1900 is to test for a date >= 1901-01-01.
Here's some code to reproduce a result that *may* be what you want:
CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1 SMALLDATETIME,
column2 SMALLDATETIME)
INSERT INTO MyTable VALUES (1,'20030101','20030101')
INSERT INTO MyTable VALUES (2,'20030101','20030102')
INSERT INTO MyTable VALUES (3,'19000101','19000101')
INSERT INTO MyTable VALUES (4,'19000101','20030101')
INSERT INTO MyTable VALUES (5,'20030101','19000101')
INSERT INTO MyTable VALUES (6,'19000101',NULL)
INSERT INTO MyTable VALUES (7,NULL,'19000101')
INSERT INTO MyTable VALUES (8,'20030101',NULL)
INSERT INTO MyTable VALUES (9,NULL,'20030101')
INSERT INTO MyTable VALUES (10,NULL,NULL)
SELECT *
FROM MyTable
WHERE
COALESCE(column1,'19000101') <> COALESCE(column2,'19000101')
AND
(column1 >= '19010101' OR column2 >= '19010101')
Which returns the rows where Keycol is 2,4,5,8 and 9.
If that's not it and you need more help then you'll have to specify which
rows you want to be included in your result. I should have covered all the
relevant combinations.
Hope this helps.
David Portas
--
Please reply only to the newsgroup
--|||Thanks a lot..........
James.
quote:
>--Original Message--
>It helps if you can post some code to reproduce your
problem and show your
quote:
>required results. It's not clear to me exactly what
result you're trying to
quote:
>get in this case.
>When combining AND and OR you have to watch out for the
order of precedence.
quote:
>Use parentheses to make the order of evaluation clear
otherwise x OR y AND z
quote:
>is evaluated as x OR (y AND z).
>Don't use LIKE to compare dates. LIKE performs a string
comparison and if
quote:
>you use it on dates then every date has to be cast as a
string. Best way to
quote:
>eliminate the year 1900 is to test for a date >= 1901-01-
01.
quote:
>Here's some code to reproduce a result that *may* be what
you want:
quote:
>CREATE TABLE MyTable (keycol INTEGER PRIMARY KEY, column1
SMALLDATETIME,
quote:
>column2 SMALLDATETIME)
>INSERT INTO MyTable VALUES (1,'20030101','20030101')
>INSERT INTO MyTable VALUES (2,'20030101','20030102')
>INSERT INTO MyTable VALUES (3,'19000101','19000101')
>INSERT INTO MyTable VALUES (4,'19000101','20030101')
>INSERT INTO MyTable VALUES (5,'20030101','19000101')
>INSERT INTO MyTable VALUES (6,'19000101',NULL)
>INSERT INTO MyTable VALUES (7,NULL,'19000101')
>INSERT INTO MyTable VALUES (8,'20030101',NULL)
>INSERT INTO MyTable VALUES (9,NULL,'20030101')
>INSERT INTO MyTable VALUES (10,NULL,NULL)
>SELECT *
> FROM MyTable
> WHERE
> COALESCE(column1,'19000101') <> COALESCE
(column2,'19000101')
quote:
d">
> AND
> (column1 >= '19010101' OR column2 >= '19010101')
>Which returns the rows where Keycol is 2,4,5,8 and 9.
>If that's not it and you need more help then you'll have
to specify which
quote:
>rows you want to be included in your result. I should
have covered all the
quote:
>relevant combinations.
>Hope this helps.
>--
>David Portas
>--
>Please reply only to the newsgroup
>--
>
>.
>
No comments:
Post a Comment