>Hi all,
>
>Today I ran across an interesting problem in a stored procedure I've inherited. The query was basically like this:
>

>INSERT INTO someTable
>    (fld1, fld2, fld3, fld4, fld5, fld6, ...)
>SELECT fld1, fld2, fld3, 11 as fld4, sum(fld5) as fld5, sum(fld6) as fld6 ...
>    FROM (
>        SELECT fld1, fld2, fld3, 11 as fld4, fld5, fld6 ...
>            FROM subTable1 WITH (NOLOCK)
>            WHERE someCondition
>        UNION ALL
>        SELECT fld1, fld2, fld3, 11 as fld4, fld5, fld6 ...
>            FROM subTable2 WITH (NOLOCK)
>            WHERE someCondition
>        UNION ALL
>        SELECT fld1, fld2, fld3, 11 as fld4, fld5, fld6 ...
>            FROM subTable3 WITH (NOLOCK)
>            WHERE someCondition
>        UNION ALL
>        SELECT fld1, fld2, fld3, 11 as fld4, fld5, fld6 ...
>            FROM subTable4 WITH (NOLOCK)
>            WHERE someCondition
>        UNION ALL
>    ) SI
>    GROUP BY fld1, fld2, fld3
>    HAVING fld5 <> 0 OR fld6 <> 0 ...
>

>someTable has a primary key of (fld1, fld2, fld3, fld4)
>The insert fails because of primary key violations.
>If I execute just the SELECT portion, sure enough, there are places where two rows exist that should be aggregated together. If I take the SELECT portion and wrap another SELECT...GROUP BY around it, it's fine. Also, if I run the SELECT portion into a TEMP table, then issue the INSERT...SELECT...FROM TEMP TABLE...GROUP BY... it works fine.
>
>Has anyone seen any instances like this where grouping union all'd subqueries don't group properly? And if so, is there a proper work around. Right now, I'm using the TEMP table solution, but I'm curious to know if it's the right way or not.
>
>TIA,
>Chad

Chad,

I think that the HAVING clause is throwing the query out of whack. It looks like the aggregate functions are missing in the clause. If the clause was changed to the aggregates instead of the source columns, the optimizer would be forced to use the GROUP BY on the outer query.