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

Question

 

 

Hi,

 

When I run a aggregate query, the null values are eliminated , is there a way to get the all values including NULLs as well? Thanks,

 

below is my sample script

 

SELECT DISTINCT

TF.LEAD_ID,

MAX(DT.)           AS A,

MAX(BMP.B)     AS B,

MAX(PC.C)       AS C,

MAX(BM.D) AS D

--INTO #TEMP_FALL_OWNER_NULL

FROM TableA TF

 

INNER JOIN tABLES

 

GROUP BY TF.LEAD_ID

Answer 1

The message means that NULL values are not included in the MAX() command you issued.  What would you like it to do?  A NULL value is undefined.  So it can't add the values to MAX() as they have no value.

Answer 2

 

 

No, I have to put the all leads into the output even if their max date is null.

 

Answer 3

Use LEFT JOIN instead of INNER JOIN.

Answer 4

If you are missing records then you need to use a left join as mentioned or otherwise define the query differently.  The warning/error message you get you still may receive, because as long as there are null values in some of the rows of the columns used in the MAX() function you will get that notice.

 

 

Answer 5

 

Ok, I used  ISNULL func, that works. Thanks.

 

Answer 6

Hi ,

   I have used the same .Its worked fine for me.But still the error occurs.

---

Ravishankar Maduri MCTS,MCPD,MCP

Answer 7

Hi,

 

     dont forget to use ISNULL inside MAX() aggregates

 

     ex.   

        MAX(ISNULL(BMP.B,0))

Answer 8

Hi guys, for does who just want to hide the message, use SET ANSI_WARNINGS OFF;

Answer 9

Thank you Ilian.  This was what I needed, which was a way to get rid of the warning message, since the behaviour is what I intended and my SSIS package was failing due to the warning.

---

Katy Lynn McCullough-Leonard