Null value is eliminated by an aggregate or other SET operation

Question

Post reply

Null value is eliminated by an aggregate or other SET operation

lapus014

Old Hand

Points: 340
More actions
January 7, 2024 at 7:41 am

#4357972

Should I see this warning when I have ISNULL check at SUM function and changing NULLs to 0?

Thank you

Viewing 5 posts - 1 through 4 (of 4 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

lapus014 wrote:

Should I see this warning when I have ISNULL check at SUM function and changing NULLs to 0?
Thank you

No. As demonstrated below.

DROP TABLE IF EXISTS #x;

CREATE TABLE #x
(
    SomeValue INT
);

INSERT #x
(
    SomeValue
)
VALUES
(NULL)
,(1)
,(2);

SELECT TotValue = SUM (ISNULL (x.SomeValue, 0))
FROM #x x;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Phil Parkin SSC Guru Points: 246960 More actions · Answer 2

--Removed duplicate

This reply was modified 10 months, 2 weeks ago by Phil Parkin.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

lapus014 wrote:

Should I see this warning when I have ISNULL check at SUM function and changing NULLs to 0?

As Phil pointed out - you can eliminate that message. If you are still seeing the message - then it is most likely because of where you placed the ISNULL function.

For any further help - we would need to see the code you are executing where you are using ISNULL and still getting that warning.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

frederico_fonseca SSCoach Points: 16154 More actions · Answer 4

well... what Phil posted will remove the message - but the other way you may be using the isnull will not

so if you do the alternative to Phil's. where you put the isnull OUTSIDE the sum, that will still give you the message - so likely this is what you are doing.

SELECT TotValue = ISNULL (SUM (x.SomeValue), 0)
FROM #x x;