How can I solve Null value is eliminated by an aggregate or other SET operation?

Question

How can I solve Null value is eliminated by an aggregate or other SET operation?

bkshn

Right there with Babe

Points: 794
More actions
November 26, 2013 at 6:23 am

#279403

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?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

bkshn Right there with Babe Points: 794 More actions · Answer 2

bkshn

Right there with Babe

Points: 794

November 26, 2013 at 6:47 am

#1669127

Thanks alot it works! 🙂

Keith Tate SSCrazy Eights Points: 9888 More actions · Answer 3

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'

Microsoft Certified Master - SQL Server 2008
Follow me on twitter: @keith_tate

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Hunchback SSCrazy Points: 2649 More actions · Answer 4

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'

Lowell SSC Guru Points: 323504 More actions · Answer 5

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

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').

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Hunchback SSCrazy Points: 2649 More actions · Answer 7

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.