Post reply

"Null value is eliminated by an aggregate" Error without aggregates

  • January 1, 2012 at 8:24 am

    #248375

    I'm a little confused. I have query with 3 tables (two of them left outer joined) that has no aggregates or set operations. And yet, because the last two columns (from the third joined table) do have at least 1 null value, I'm getting this error:

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

    SELECT Distinct D.Col1, D.Col2, D.Col3, D.Col4, D.Col5, D.Co6, D.Col7,

    Agt.Col1, Agt.Col2, AH.Col1, AH.Col2

    FROM Table1 AS D

    LEFT OUTER JOIN Table2 AS Agt

    ON D.FromDate = Agt.FromDate AND D.CCode = Agt.CCode

    AND D.Ins = Agt.Ins AND D.Entity = Agt.Entity

    LEFT OUTER JOIN Table3 AS AH

    ON D.Entity = AH.Entity AND D.Ins = AH.Ins

    AND D.CCode = AH.CCode AND D.FromDate = AH.FromDate

    ORDER BY D.Entity, D.CCode, D.FromDate

    Is this a bug in 2005 that I just haven't seen before?

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • January 1, 2012 at 9:07 am

    #1427274

    I'd say it's because of the Distinct operator in your query. Therewith you're using an aggregation similar to

    GROUP BY D.Col1, D.Col2, D.Col3, D.Col4, D.Col5, D.Co6, D.Col7,

    Agt.Col1, Agt.Col2, AH.Col1, AH.Col2

    I wouldn't consider it a bug but rather a correct warning.


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • January 2, 2012 at 1:53 am

    #1427333

    Brandie Tarvin (1/1/2012)

    ...Is this a bug in 2005 that I just haven't seen before?

    Hi Brandie, Lutz is spot on; the warning indicates how DISTINCT is processed.

    Paul White has a cracking good blog on DISTINCT with aggregates which begins with a solid introduction to DISTINCT here.

    Cheers

    Chris

    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].

    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • January 2, 2012 at 6:24 am

    #1427416

    Thank you both. It never occurred to me that SQL would treat DISTINCT the same way it treats aggregate functions (via error code), even though I know the basics of how the keyword works.

    I have learned something new this weekend.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • January 2, 2012 at 7:36 am

    #1427428

    Brandie Tarvin (1/2/2012)

    ...

    I have learned something new this weekend.

    I can rephrase it for myself... hmmm Weekdays / Working Hours? :Whistling:

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

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