September 8, 2011 at 9:11 am
i am getting the following warning on running my query:-
Warning: The join order has been enforced because a local join hint is used.
Warning: Null value is eliminated by an aggregate or other SET operation.
the query has a union from subqueries .tables in some subqueries do not have corresponding cols in other subquery,hence they are written as null..the subquery has group by condition but for all the columns in group by condition,i have handled them with isnull(x,y) function.
cannot ignore this warning,since when i run the query through a stored proc in ssis package,it fails
September 8, 2011 at 9:34 am
"Warning: Null value is eliminated by an aggregate or other SET operation"
This is probably caused by having ANSI_WARNINGS on and an aggregate on a column which contains a null value.
e.g.
SET ANSI_WARNINGS ON
DECLARE @TABLE AS TABLE(Col1 INT, Col2 INT)
INSERT INTO @TABLE(Col1, Col2)
SELECT 1, 1 UNION ALL
SELECT 1, 2 UNION ALL
SELECT 1, 3 UNION ALL
SELECT 2, 1 UNION ALL
SELECT 2, Null UNION ALL
SELECT 2, 3
SELECT Col1, SUM(Col2), COUNT(Col2), AVG(Col2)
FROM @TABLE
GROUP BY Col1
So, you can eliminate it by setting ANSI_WARNINGS off or eliminating the NULL values from the query.
"Warning: The join order has been enforced because a local join hint is used"
This is because you've added a HINT to a join. The chances are pretty good that doing this was wrong, almost all of the time the optimiser knows what it's doing and should be left alone. If you're in one of the very very rare cases where this is not applicable, you could add OPTION (FORCE ORDER) to your query to stop SQL Server warning you that it is going to enforce the ordering.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply