Friday, September 12, 2008 2:40 PM
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
MAX(DT.) AS A,
MAX(BMP.B) AS B,
MAX(PC.C) AS C,
MAX(BM.D) AS D
GROUP BY TF.LEAD_ID
Friday, September 12, 2008 2:58 PM
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 3:01 PM
No, I have to put the all leads into the output even if their max date is null.
Friday, September 12, 2008 7:30 PM
Use LEFT JOIN instead of INNER JOIN.
Friday, September 12, 2008 7:52 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 19, 2008 3:10 PM
Ok, I used ISNULL func, that works. Thanks.
Monday, December 21, 2009 12:07 PMHi ,I have used the same .Its worked fine for me.But still the error occurs.
Ravishankar Maduri MCTS,MCPD,MCP
Tuesday, May 10, 2011 6:57 AM
dont forget to use ISNULL inside MAX() aggregates
Friday, April 20, 2012 6:36 AMHi guys, for does who just want to hide the message, use SET ANSI_WARNINGS OFF;
- Proposed As Answer by Katy Leonard Thursday, July 19, 2012 8:32 PM
Thursday, July 19, 2012 8:33 PMThank 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