none
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

    Friday, September 12, 2008 2:40 PM

Answers

All replies

  • 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.

    Friday, September 12, 2008 2:58 PM
  •  

     

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

     

    Friday, September 12, 2008 3:01 PM
  • Use LEFT JOIN instead of INNER JOIN.

    Friday, September 12, 2008 7:30 PM
  • 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.

     

     

    Friday, September 12, 2008 7:52 PM
  •  

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

     

    Friday, September 19, 2008 3:10 PM
  • Hi ,

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



    Ravishankar Maduri MCTS,MCPD,MCP
    Monday, December 21, 2009 12:07 PM
  • Hi,

     

         dont forget to use ISNULL inside MAX() aggregates

     

         ex.   

            MAX(ISNULL(BMP.B,0))

    • Proposed as answer by IlianMarinov Friday, April 20, 2012 6:34 AM
    • Unproposed as answer by IlianMarinov Friday, April 20, 2012 6:34 AM
    • Proposed as answer by Ken in Tampa Tuesday, May 08, 2012 5:04 PM
    Tuesday, May 10, 2011 6:57 AM
  • Hi guys, for does who just want to hide the message, use SET ANSI_WARNINGS OFF;
    Friday, April 20, 2012 6:36 AM
  • 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

    Thursday, July 19, 2012 8:33 PM
  • I have a question here.  If I set these warnings off, are there any, um, "real" error messages that I will not see.  I don't consider this a "real" error - this is desirable behavior as far as I am concerned.  Filtering out the nulls before trying to do an average is very common practice in the work environments I have been in for the last 30 years.


    Friday, June 14, 2013 12:53 PM
  • Please, please, please read any book on SQL! Ever since the SQL-86 Standards, in every implementation the language NULLs are dropped from aggregates. This is like no understanding how zero works in math. 

    --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

    Friday, June 14, 2013 1:19 PM
  • Please, please, please read any book on SQL!

    Maybe you should calm down.  I asked a legitimate question.

    Ever since the SQL-86 Standards, in every implementation the language NULLs are dropped from aggregates.

    That is exactly how I have been calculating aggregates for 30 years, before SQL was as common as it is now.  (Remember hierarchical databases?)  I said that I didn't consider this a useful 'warning' but didn't want to disable it in case I disabled something else.

    This is like no understanding how zero works in math.

    Maybe I have no understanding of how math works, or zeroes, or nulls, but somehow I have been working with computers before there were PCs.  Go figure.

    Friday, June 14, 2013 1:31 PM
  • I can think of 2 uses for this warning: (1) it reminds us that nulls are present and allowed in the source column; (2) it alerts us to the presence of data or logic errors in situations where nulls should not be expected.

    Persisting a non-standard practice on the basis of 30 years' precedence won't win accolades from your successors.

    
    Monday, June 17, 2013 1:40 AM
  • I was having trouble too.  However, my issue was not due to a NULL value.  Mine was due to the fact that I misspelled the field name.  Once I corrected the name, the script worked for me.
    Friday, July 26, 2013 7:52 PM
  • INV_Informe_Precios_Bodega  0.00,'',''

    This stored I want to show the result

    Friday, May 09, 2014 7:57 PM
  • I can think of 2 uses for this warning: (1) it reminds us that nulls are present and allowed in the source column; (2) it alerts us to the presence of data or logic errors in situations where nulls should not be expected.

    Persisting a non-standard practice on the basis of 30 years' precedence won't win accolades from your successors.

    

    Actually, there is a good story about this :) Back when I was on X3H2, Len Gallagher from NIST had summer interns who tested this in all the SQLs we had back then. There was a question of how a cursor would work when a group from a GROUP BY was read. Should the warning about the group having a NULL appear when we DECLARE CURSOR?  when we OPEN CURSOR (many SQLs did not do the query until then)? The first FETCH INTO (many SQLs did not do the query until then)? Each FETCH INTO (actually, this is required)? 

    It is implementation defined! This is an ANSI/ISO cop-out :)

    The example was a report grouping the sales by teams; some of the teams have a salesman who failed to turn in his report (sales_amt IS NULL). Do you do the report at all? Is NULL the same as $0.00 in sales? Is the team's sum NULL? 

    But back to the topic, if (A + B +..+ NULL) = NULL, then SUM(<numeric expression>) = NULL when any value in the set is NULL. That is why we drop them. 

    And as a purist, NULL is not a value! it is the lack of any value. SQL has to cast them to a data type for columns, but we are violating Codd's RM rules. 


    --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

    Saturday, May 10, 2014 8:57 PM