Warning: Null value is eliminated by an aggregate or other SET operation ?

Question

I working on SQL server 2012 I face issue I can't solve this warning 

Warning: Null value is eliminated by an aggregate or other SET operation 

so How to solve this warning and not display again 

my query

  UPDATE FFFF
SET
Conflictflag= IIF((NotNULL+NuLLCount)<>RowsCount AND Ex.MaskExceptionID IS NULL ,CONCAT(Conflictflag,'ComplianceID','|'),Conflictflag),
NULLflag=IIF((NotNULL+NuLLCount)=RowsCount AND NuLLCount>0 AND NULEX.NULLExceptionID IS NULL,CONCAT(NULLflag,'ComplianceID','|'),NULLflag)
FROM
(
SELECT Masked_ID,SUM(CNT)/COUNT(Compliance_Status_ID) AS NotNULL ,SUM(NULLCount)AS NuLLCount
FROM (
SELECT FF.Masked_ID,  LC.Compliance_Status_ID,
COUNT(DISTINCT LC.Zpart_ID) AS CNT,--COUNT(CASE WHEN DocumentID IS NOT NULL THEN 1 ELSE NULL END) AS CNT,
COUNT( CASE WHEN Compliance_Status_ID IS NULL THEN 1 ELSE NULL END )NULLCount
FROM ExtractReports.dbo.MultiMask FF
INNER JOIN Parts.Nop_Part ptt WITH(NOLOCK) ON ptt.Masked_ID=ff.Masked_ID --AND FF.Masked_ID=287578
--LEFT JOIN PCN.DocumentParts LC WITH(NOLOCK) ON ptt.PartID=LC.PartID
left JOIN DocumentCompliance.Output LC WITH(NOLOCK) ON ptt.PartID=LC.Zpart_ID
WHERE FF.ComplianceID LIKE '%|%'  
GROUP BY FF.Masked_ID ,LC.Compliance_Status_ID
)DD
GROUP BY DD.Masked_ID
) DDFF
INNER JOIN  ExtractReports.dbo.MultiMask FFFF ON  DDFF.Masked_ID=FFFF.Masked_ID
LEFT JOIN [ConflictReport].dbo.MaskExceptions EX ON EX.MaskID=FFFF.Masked_ID AND EX.FunctionName='ComplianceID'
LEFT JOIN [ConflictReport].dbo.NULLExceptions NULEX ON NULEX.MaskID=FFFF.Masked_ID AND NULEX.FunctionName='ComplianceID'
WHERE --DDFF.SUMC <>FFFF.RowsCount AND
FFFF.ComplianceID LIKE '%|%' 

so how to solve this issue please ?

Answer 1

Hi engahmedbarbary，

Please have a try with query modification like below example:

COUNT( CASE WHEN Compliance_Status_ID IS NULL THEN 1 ELSE NULL END ) NULLCount

---modify above like below --->
SUM( CASE WHEN Compliance_Status_ID IS NULL THEN 1 ELSE 0 END ) NULLCount

In addition, NULLs are being ignored because we are using aggregate function (SUM, AVG). To avoid the warning we could use “set ansi_warnings off” before the script.

Here is the modified script.

SET ANSI_WARNINGS OFF
GO

Best regards

Melissa

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 2

I working on SQL server 2012 I face issue I can't solve this warning 

Warning: Null value is eliminated by an aggregate or other SET operation 

so How to solve this warning and not display again 

You ignore it. This is white noise produced for some ANSI-compliant reason. The message is irritating, but as I said, just ignore it.

As Melissa said, you can get rid of the warning if you do SET ANSI_WARNINGS OFF. DON'T DO THIS! NEVER! There is functionality in SQL Server that requires this setting to be ON, and you can face other errors if you issue this command. And they are errors, not warnings.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 3

Hi engahmedbarbary，

Thanks Erland for pointed out my misunderstanding.

After searching, when ANSI_WARNINGS is set to ON, the engine follows the standard ISO behavior for the following situations: 
1.Encountering a NULL value during an aggregation operation
2.Encountering a Divide by Zero error
3.String truncation

When ANSI_WARNINGS is OFF, the engine follows a non-standard behavior, which reduces data quality and depending upon your business context, may generate bad data. 

Please refer more details from below links and check whether ANSI_WARNINGS could be off from your side.

SET ANSI_WARNINGS (Transact-SQL)

What is the impact of setting SET ANSI_WARNINGS OFF?

After all, please try with below firstly and check whether it could be helpful to you.

COUNT( CASE WHEN Compliance_Status_ID IS NULL THEN 1 ELSE NULL END ) NULLCount

---modify above like below --->
SUM( CASE WHEN Compliance_Status_ID IS NULL THEN 1 ELSE 0 END ) NULLCount

Best regards

Melissa

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.

Answer 4

Maybe also consider expressions like this: COUNT(ISNULL(column, 0)), COUNT(DISTINCT ISNULL(column,  0)), SUM(ISNULL(column, 0)).

But the results could be different after such changes. Maybe it is better to ignore the warning, depending on your needs.

Answer 5

Maybe also consider expressions like this: COUNT(ISNULL(column, 0)), COUNT(DISTINCT ISNULL(column,  0)), SUM(ISNULL(column, 0)).

That will not give the same results.

SELECT COUNT(col) FROM tbl

returns the number of rows where col is non-NULL. If you say

SELECT COUNT(isnull(col, 0))) FROM tbl

this will of course be the same as COUNT(*). You can work around it with using SUM as Melissa suggested, but you only make the code more complex. I don't know why ANSI mandates this warning, but as I said, just ignore it. Treat it as white noise.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 6

You need to sit down and actually read a book on SQL. Ever since the 1986 language standards, when a group is aggregated (SUM, AVG, MIN, MAX, COUNT, COUNT(*)) . The NULL rows are removed before the computation is done. The reason you get the warning is so that you can tell the host program. If the computation is based on a complete set or set with missing values. For example, say you were something the sales by salesman, but somebody didn't turn in his report so he has a total of NULL sales (remember that NULL is not the same as zero dollars worth of sales). This should have been covered by the second or third week of any decent class on SQL.

Frankly, though I don't want to go into details, the rest of your code is a mess. You write with flags in a language that is based on predicates. Use more NULLs in this one statement than I generally use in a schema for a major corporation. You don't know how to name a data element. Please think about how silly something like "compliance_status_id" is; not only does it violate ISO 11179 naming rules but where is the compliance_status? Where only seeing the identifier for the status! If you get a chance read Lewis Carroll's children's book "Through the Looking Glass" in which the white knight gives a speech about the name of the name of the name of a song. It illustrates this design flaw very nicely as well as being classic literature . You also fail to write SQL why are you using the dialect IFF() instead of the ANSI/ISO standard case expression? Do you want your code to be non-portable and non-standard. You're also using the old update dialect rather than the ANSI/ISO standard merge statement. Without any DDL I cannot turn this into SQL from your dialect. In fact, I can even make a good guess at what you're trying to do.

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 7

>>  I don't know why ANSI mandates this warning, but as I said, just ignore it. Treat it as white noise.  <<

Ever since the 1986 language standards, when a group is aggregated (SUM, AVG, MIN, MAX, COUNT, COUNT(*)) . The NULL rows are removed before the computation is done. The reason you get the warning is so that you can tell the host program if the computation is based on a complete set or set with missing values. For example, say you were totalling the sales by salesman, but somebody didn't turn in his report so he has a total of NULL sales (remember that NULL is not the same as zero dollars worth of sales).  

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL