SQLTeam.com | Weblogs | Forums

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

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 as below :slight_smile:

 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 ?

Why is it a problem, in this particular case, to have the warning appear?

hi

please see below link ..
in that link how eliminate warning message is there ..
Hope this helps

https://blog.sqlauthority.com/2015/02/13/sql-server-warning-null-value-is-eliminated-by-an-aggregate-or-other-set-operation/

You may not actually want to do a "SET ANSI_WARNINGS OFF" if you can help it. If it's off for your session (SSMS and multiple providers turn it on automatically), any inserts, updates, or deletes will fail on tables that have indexed computed columns and any indexed views. There are other "problems" that can occur if you have it OFF. Please see the following Microsoft documentation.

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-ver15

1 Like

One idea is

Set it on off at the beginning of Script

and

Set it back ON at end of script

Jeff .. your thoughts on this would be really appreciated !! as they are always Insightful