Post reply

using MAX function

  • October 20, 2008 at 7:13 am

    #214262

    Hello,

    While running a query,I get warning message saying

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

    However, I get some records as well. So, does this means I am getting those records where NULL condition does not exists.

    I think I am getting this message as I am using MAx function of some of the columns.

    Is there any way to determine which column is causing this message. Would it be because of only those columns on which MAx is applied OR it could be result of other column.

    One more thing, I am using MAX on column with datetime datatype and it contains lots of NULLs. Can this be reason? Or could it be result of having [blanks] in char datatype column?

    Thanks

  • October 20, 2008 at 7:37 am

    #887104

    You will only get that message if there are NULL values. Empty strings in a character field will not give you that message.

    It can be from the date column you are using MAX() on. For MAX(), it will return the maximum non-null value in the column. So, if you have at least one value that is not null, the message does not mean much to you. If you happened to have NULL for all rows, it would return no rows rather than returning one row with a NULL.

    This message is typically more important with aggregates like AVG() - which will not include the NULL rows when it averages.

    You should read up on aggregates and NULL in books online.

  • October 20, 2008 at 7:39 am

    #887108

    The MAX() and other functions need a value to evaluate. The nature of NULL is that it is undefined. So if you have three records with quantities like 1, 5, 4 the MAX(Quantity) will return 5. If you had values like 1, NULL, 4 then it would return 4. The warning message is just stating that the NULL value could not be included because it can't evaluate it since it is undefined.

    If you want to find what rows are causing the issue look for records WHERE [ColumnName] IS NULL. Obviously the [ColumnName] is the column used in the max function.

    Hope it helps.

  • October 20, 2008 at 7:43 am

    #887113

    Thanks for such a quick response.

    Thank you!

  • October 21, 2008 at 6:36 am

    #887551

    In most cases you can disregard that message, but you will always be wondering if there is a bug in your code. Also, if you happen to be using MSSQL 2005 + Vista + Access those messages are returned to the Access app where they create 1-row tables with that message.

    The best approach is to examine your data & determine which columns have nulls and do a little re-write. The only way I found to prevent these messages was to step through the T-SQL in SSMS and locate & fix the offending code. For example, there might have been a statement

    SUM(Payment) AS TotalPaid

    which had to be revised as

    SUM(ISNULL(Payment,0)) AS Total Paid

    Another example that I found was a statement like

    COUNT(DISTINCT InvoiceID)

    in which case I had to add a statement

    WHERE InvoiceID IS NOT NULL

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply