How can I solve Null value is eliminated by an aggregate or other SET operation?

  • Hello

    I execute the code below

    declare @LastDate datetime

    SELECT @LastDate = max([LastUpdate])

    FROM [exhibitor].[dbo].[blgBelongs]

    WHERE (([Table1]=@module1 OR [Table2]=@module2 )or ([Table2]=@module1 OR [Table1]=@module2 )

    AND Exists (SELECT [Table1],[Table1ID] FROM [exhibitor].[dbo].[blgBelongs] WHERE table2=30 and table2ID=@dmn_ID))

    Befor I see @LastDate , I see this warning

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

    Could you help me how can I solve it?

  • there's nothing wrong with receiving that warning; some of the data you are looking at is null, and that's normal; some data has never been updated, only origianlly inserted is my guess.

    if you REALLY wnat to get rid of the warning,

    eliminate the null in the data that it is checking.

    SELECT @LastDate = max(ISNULL([LastUpdate],'1900-01-01'))

    ...

    or use a WHERE statement:

    WHERE ...

    AND [LastDate] IS NOT NULL

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks alot it works! 🙂

  • or use a WHERE statement:

    WHERE ...

    AND [LastDate] IS NOT NULL

    If you want to exclude dates of 1900-01-01 then you will need to add:

    WHERE ...

    AND [LastDate] IS NOT NULL

    and [LastDate] <> '19000101'



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • There is no harm by having both expressions but having "LastDate <> '19000101'" is enough to cover both cases "is not NULL and is diff from '19000101'".

    WHERE ...

    [LastDate] <> '19000101'

  • hunchback (11/26/2013)


    There is no harm by having both expressions but having "LastDate <> '19000101'" is enough to cover both cases "is not NULL and is diff from '19000101'".

    WHERE ...

    [LastDate] <> '19000101'

    not true; NULL can never be evaluated against a value, since it's undefined.

    you must ALWAYS check for null using IS NULL / IS NOT NULL, unless you converted the value (which you want to avoid, it causes a table scan)

    ;WITH MyCTE([LastDate])

    AS

    (

    SELECT convert(datetime,'2013-11-14 09:25:42.257') UNION ALL

    SELECT '1900-01-01' UNION ALL

    SELECT NULL UNION ALL

    SELECT '2013-06-02 14:12:09.527'

    )

    SELECT * FROM MyCTE WHERE LastDate <> '1900-01-01'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (11/26/2013)


    hunchback (11/26/2013)


    There is no harm by having both expressions but having "LastDate <> '19000101'" is enough to cover both cases "is not NULL and is diff from '19000101'".

    WHERE ...

    [LastDate] <> '19000101'

    not true; NULL can never be evaluated against a value, since it's undefined.

    NULL CAN be evaluated against a value and it will return undefined which is different from true and that's why LastDate <> '19000101' is enough to validate "non-empty" dates. It would be different if he was looking to return the opposite set of values (NULL & '1900-01-01').

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • I would suggest to read the predicate expressions one more time.

    ...

    Where dt is not NUL and dt <> '19000101';

    can be simplified as:

    ...

    Where dt <> '19000101';

    As you can see from your own script. Any Comparison to The NULL mark using logical operators, except IS [NOT] NULL, will yield the NULL mark which is not true either false.

Viewing 8 posts - 1 through 7 (of 7 total)

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