November 26, 2013 at 6:23 am
Hello
I execute the code below
declare @LastDate datetime
SELECT @LastDate = max([LastUpdate])
FROM [exhibitor].[dbo].[blgBelongs]
WHERE (([Table1]=@module1 OR [Table2]=@module2 )or ([Table2]=@module1 OR [Table1]=@module2 )
AND Exists (SELECT [Table1],[Table1ID] FROM [exhibitor].[dbo].[blgBelongs] WHERE table2=30 and table2ID=@dmn_ID))
Befor I see @LastDate , I see this warning
Warning: Null value is eliminated by an aggregate or other SET operation.
Could you help me how can I solve it?
November 26, 2013 at 6:34 am
there's nothing wrong with receiving that warning; some of the data you are looking at is null, and that's normal; some data has never been updated, only origianlly inserted is my guess.
if you REALLY wnat to get rid of the warning,
eliminate the null in the data that it is checking.
SELECT @LastDate = max(ISNULL([LastUpdate],'1900-01-01'))
...
or use a WHERE statement:
WHERE ...
AND [LastDate] IS NOT NULL
Lowell
November 26, 2013 at 6:47 am
Thanks alot it works! 🙂
November 26, 2013 at 7:27 am
or use a WHERE statement:
WHERE ...
AND [LastDate] IS NOT NULL
If you want to exclude dates of 1900-01-01 then you will need to add:
WHERE ...
AND [LastDate] IS NOT NULL
and [LastDate] <> '19000101'
November 26, 2013 at 9:15 am
There is no harm by having both expressions but having "LastDate <> '19000101'" is enough to cover both cases "is not NULL and is diff from '19000101'".
WHERE ...
[LastDate] <> '19000101'
November 26, 2013 at 9:20 am
hunchback (11/26/2013)
There is no harm by having both expressions but having "LastDate <> '19000101'" is enough to cover both cases "is not NULL and is diff from '19000101'".
WHERE ...
[LastDate] <> '19000101'
not true; NULL can never be evaluated against a value, since it's undefined.
you must ALWAYS check for null using IS NULL / IS NOT NULL, unless you converted the value (which you want to avoid, it causes a table scan)
;WITH MyCTE([LastDate])
AS
(
SELECT convert(datetime,'2013-11-14 09:25:42.257') UNION ALL
SELECT '1900-01-01' UNION ALL
SELECT NULL UNION ALL
SELECT '2013-06-02 14:12:09.527'
)
SELECT * FROM MyCTE WHERE LastDate <> '1900-01-01'
Lowell
November 26, 2013 at 11:03 am
Lowell (11/26/2013)
hunchback (11/26/2013)
There is no harm by having both expressions but having "LastDate <> '19000101'" is enough to cover both cases "is not NULL and is diff from '19000101'".
WHERE ...
[LastDate] <> '19000101'
not true; NULL can never be evaluated against a value, since it's undefined.
NULL CAN be evaluated against a value and it will return undefined which is different from true and that's why LastDate <> '19000101' is enough to validate "non-empty" dates. It would be different if he was looking to return the opposite set of values (NULL & '1900-01-01').
November 26, 2013 at 3:59 pm
I would suggest to read the predicate expressions one more time.
...
Where dt is not NUL and dt <> '19000101';
can be simplified as:
...
Where dt <> '19000101';
As you can see from your own script. Any Comparison to The NULL mark using logical operators, except IS [NOT] NULL, will yield the NULL mark which is not true either false.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply