Warning: Null value is eliminated by an aggregate | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Warning: Null value is eliminated by an aggregate

I have a stored procedure that intermittently generates the following message on my asp page: "Warning: Null value is eliminated by an aggregate or other SET operation." The warning only appears when the procedure is run via the asp page. When I execute the same procedure with the same parameters on the same database through Query Analyzer it returns a result set. There is an MS Knowledge Base article at http://support.microsoft.com/default.aspx?scid=kb;en-us;317312 which appears to recognize the problem, but gives no hint as to how I solve it. The likely code culprit is in here somewhere: (SELECT SUM(CASE
WHEN b.EndDt <= dbo.get_max_status_date(@intAreaID, ‘prog’) THEN b.budget
WHEN b.StartDt >= dbo.get_max_status_date(@intAreaID, ‘prog’) THEN 0
ELSE (b.budget/DATEDIFF(dy, b.StartDt, b.EndDt)) * DATEDIFF(dy, b.StartDt, dbo.get_max_status_date(@intAreaID, ‘prog’))
END) FROM #PlannedValue b
WHERE b.itemGroupID = a.projectID) AS currentplan
Problem is that either b.budget or b.budget/DATEDIFF(dy, b.StartDt, b.EndDt)) * DATEDIFF(dy, b.StartDt, dbo.get_max_status_date(@intAreaID, ‘prog’) returns null.
wrap it with isnull as in WHEN b.EndDt <= dbo.get_max_status_date(@intAreaID, ‘prog’) THEN ISNULL(b.budget,0) Bambola.
It looked like a good answer, and b.budget was in fact returning some nulls. I forced b.budget to return zero’s with an isnull statement, but the problem still exists. What really bothers me is that this problem only occurs in the active server page application it was build for. If I run the query in QA it works fine, if I execute the stored procedure it works fine, or if I access my hosts’ database through their web interface it also works fine. I’m wondering if this isn’t really a SQL error, and my error tracking system or ADO just thinks it is.
could it be that one of your user defined functions is returning NULL also ? Maybe b.StartDt is NULL ? Try removing your aggregate and running in query analyser so you can see the full result set before the aggregate is applied. The fact it only occurs in ASP.. I suspect it may actually be occurring all the time, but you only get a warning depending on your connection properties. Could it be that SET ANSI_WARNINGS is ON in your ASP Connection, but your default Query analyser connection is OFF? (seems unlikely because its uncommon for people to change theyre default QA connection properties, but I imagine the problem is something like this).
None of the functions is returning null, and there are no null values in the target tables after I forced the budget field to zero. It’s almost like the query optimizer changed the way the query runs (which is what the knowledge base article says happens). SQL itself is OK with this, and ignores the warning message. However, ADO picks it up when I error check and stops processing on the ASP page. This is particularly strange, because my hosting service uses ADO to connect to the databases they support. So it’s only the ADO connection that I am using that is catching the warning. I did find out that it is a warning, not an error, with number 0. ansi warnings are on when you execute in Query Analyzer. I’m going to delve into the ADO book. If I put "On Error Resume Next" before the query command runs, then the system locks up if I force it to ignore the error. It’s like ADO just stops when it gets this warning.
Does it give the error every time you run the ASP page or is it intermittent? Does it give the error right away when you try to populate the recordset or does it wait until you try to read from it? Also, have you tried doing a SET NOCOUNT ON at the beginning of your stored procedure? I know it sounds odd but sometimes it fixes weird problems like this.
It gives the error every time I run the page, but only with 1 set of parameters. The error is generated as soon as I execute the command object. I do have SET NOCOUNT ON in a predecessor query, but not around the main query here. I’ll give that a try as well.
OK. It turns out that the first replier, bambola, was correct in their diagnosis. However, the proposed solution won’t work. The stored procedure follows these steps: 1. Create temp table 1
2. Create temp table 2
3. Populate temp table 1
4. Populate temp table 2
5. Select recordset from temp tables 1 and 2 which contains an aggregate function in the subquery. The problem is that even if you use ISNULL in steps 3, 4, and/or 5, the SQL optimizer will ignore it and write an outer join back to the original non-temp tables to get the aggregate function, thus throwing the warning. The solution being used at this point is to trap SQL errors in ADO, and any time there is a warning (error number zero) call the NextRecordset command to get the recordset. If anyone can think of a way to avoid throwing the error/warning in the first place I would really appreciate it.
Are you saying that even tho you specify that the aggregate should be based on the temporary tables, the optimiser ignores this and bases it on the real tables? Is this because you are calling UDF’s which reference the real tables? If not I have not come across this behavoir before. Are you able to post more details on your query? As it stands we can only see the subquery and a reference to a single temp table.
Given more information, we should be able to find a way to eliminate the errors by tightening the sql. I think this is the more elegant approach than relying on logic in the ADO client
It appears we have found the final solution: http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q149921. The warning can be ignored by using the SET ANSI_WARNINGS OFF in the stored procedure. Here’s the full procedure if you want to take a look. The SET ANSI_WARNING and SET ANSI_NULLS lines were inserted by the Microsoft support tech, and I haven’t had time to check and see if there is a better place to put them yet. ================ create procedure spRptHomePageEVMSubTotals
@intAreaID int,
@strAreaView char(4) AS
SET ANSI_WARNINGS OFF
SET NOCOUNT ON
–Create a temp table to hold the deliverable status
CREATE TABLE #EarnedValue (
itemID int,
itemGroupID int,
budget money) –Create a temp table to hold the planned values
CREATE TABLE #PlannedValue (
itemID int,
itemGroupID int,
StartDt datetime,
EndDt datetime,
budget money) IF @strAreaView = ‘dept’
BEGIN
— Insert EVM amounts into table
INSERT INTO #EarnedValue
SELECT
a.deliverableID,
(SELECT g1.program FROM tabDeliverables b1
INNER JOIN tabJobs d1
ON b1.jobID = d1.jobID
INNER JOIN tabOrdersJobs e1
ON d1.jobID = e1.joinJobID
INNER JOIN tabOrders f1
ON e1.joinOrderID = f1.orderID
INNER JOIN tabProjects g1
ON f1.project = g1.projectID
WHERE b1.deliverableID = a.deliverableID) AS programID,
— Get total job value where this deliverable exists
(SELECT SUM(t1.quantity * price)
FROM tabResources t1
INNER JOIN tabTasks s1
ON t1.taskID = s1.taskID
INNER JOIN tabJobs r1
ON s1.job = r1.jobID
WHERE r1.jobID =
(SELECT jobID
FROM tabDeliverables
WHERE deliverableID = a.deliverableID))/
— Divide total job value by total weighted quantity of job deliverables
(SELECT SUM(w1.quantity * metricWeight)
FROM tabDeliverables w1
INNER JOIN tabJobs v1
ON w1.jobID = v1.jobID
INNER JOIN tabMetricWeights u1
ON w1.weight = u1.metricWeightID
WHERE v1.jobID =
(SELECT jobID
FROM tabDeliverables
WHERE deliverableID = a.deliverableID))*
— Multiply result by weighted completion amount to get EV
(SELECT SUM(percentComplete * metricWeight)
FROM tabDeliverableStatus z1
INNER JOIN tabDeliverables y1
ON z1.deliverableID = y1.deliverableID
INNER JOIN tabMetricWeights x1
ON y1.weight = x1.metricWeightID
WHERE z1.deliverableID = a.deliverableID
AND z1.reportID = b.reportID) AS earnedValue
FROM tabDeliverables a
INNER JOIN tabDeliverableStatus b
ON a.deliverableID = b.deliverableID
INNER JOIN tabStatusReports c
ON b.reportID = c.statusReportID
INNER JOIN tabOrders d
ON c.orderID = d.orderID
INNER JOIN tabProjects e
ON d.project = e.projectID
INNER JOIN tabPrograms f
ON e.program = f.programID
WHERE f.departmentID = @intAreaID –Insert job values into temp table
INSERT INTO #PlannedValue
SELECT
jobID,
(SELECT g1.program FROM tabJobs d1
INNER JOIN tabOrdersJobs e1
ON d1.jobID = e1.joinJobID
INNER JOIN tabOrders f1
ON e1.joinOrderID = f1.orderID
INNER JOIN tabProjects g1
ON f1.project = g1.projectID
WHERE d1.jobID = a.jobID) AS programID,
startDate,
endDate,
(SELECT SUM(quantity * price) FROM tabResources b1
INNER JOIN tabTasks c1 ON b1.taskID = c1.taskID
WHERE c1.job = a.jobID) AS dollars
FROM tabJobs a
INNER JOIN tabOrdersJobs e1
ON a.jobID = e1.joinJobID
INNER JOIN tabOrders f1
ON e1.joinOrderID = f1.orderID
INNER JOIN tabProjects g1
ON f1.project = g1.projectID
INNER JOIN tabPrograms h1
ON g1.program = h1.programID
WHERE h1.departmentID = @intAreaID
SET NOCOUNT OFF — Query tables to get EVM stats by area
SELECT
a.programID AS fieldID,
a.progName AS fieldName,
(SELECT SUM(quantity * price) FROM tabResources b1
INNER JOIN tabTasks c1
ON b1.taskID = c1.taskID
INNER JOIN tabJobs d1
ON c1.job = d1.jobID
INNER JOIN tabOrdersJobs e1
ON d1.jobID = e1.joinJobID
INNER JOIN tabOrders f1
ON e1.joinOrderID = f1.orderID
INNER JOIN tabProjects g1
ON f1.project = g1.projectID
WHERE g1.program = a.programID) AS budget,
(SELECT SUM(a1.quantity * b1.price) FROM tabTaskStatus a1
INNER JOIN tabResources b1
ON a1.resourceID = b1.resourceID
INNER JOIN tabTasks c1
ON b1.taskID = c1.taskID
INNER JOIN tabJobs d1
ON c1.job = d1.jobID
INNER JOIN tabOrdersJobs e1
ON d1.jobID = e1.joinJobID
INNER JOIN tabOrders f1
ON e1.joinOrderID = f1.orderID
INNER JOIN tabProjects g1
ON f1.project = g1.projectID
WHERE g1.program = a.programID) AS statuscost,
(SELECT SUM(budget) FROM #EarnedValue
WHERE itemGroupID = a.programID) AS earnedvalue,
(SELECT SUM(CASE
WHEN b.EndDt <= dbo.get_max_status_date(@intAreaID, ‘dept’) THEN b.budget
WHEN b.StartDt >= dbo.get_max_status_date(@intAreaID, ‘dept’) THEN 0
ELSE (b.budget/DATEDIFF(dy, b.StartDt, b.EndDt)) * DATEDIFF(dy, b.StartDt, dbo.get_max_status_date(@intAreaID, ‘dept’))
END) FROM #PlannedValue b
WHERE b.itemGroupID = a.programID) AS currentplan FROM tabPrograms a
WHERE a.departmentID = @intAreaID
END
IF @strAreaView = ‘prog’
BEGIN
— Insert EVM amounts into table
INSERT INTO #EarnedValue
SELECT
a.deliverableID,
(SELECT f1.project FROM tabDeliverables b1
INNER JOIN tabJobs d1
ON b1.jobID = d1.jobID
INNER JOIN tabOrdersJobs e1
ON d1.jobID = e1.joinJobID
INNER JOIN tabOrders f1
ON e1.joinOrderID = f1.orderID
WHERE b1.deliverableID = a.deliverableID) AS project,
— Get total job value where this deliverable exists
(SELECT SUM(t1.quantity * price)
FROM tabResources t1
INNER JOIN tabTasks s1
ON t1.taskID = s1.taskID
INNER JOIN tabJobs r1
ON s1.job = r1.jobID
WHERE r1.jobID =
(SELECT jobID
FROM tabDeliverables
WHERE deliverableID = a.deliverableID))/
— Divide total job value by total weighted quantity of job deliverables
(SELECT SUM(w1.quantity * metricWeight)
FROM tabDeliverables w1
INNER JOIN tabJobs v1
ON w1.jobID = v1.jobID
INNER JOIN tabMetricWeights u1
ON w1.weight = u1.metricWeightID
WHERE v1.jobID =
(SELECT jobID
FROM tabDeliverables
WHERE deliverableID = a.deliverableID))*
— Multiply result by weighted completion amount to get EV
(SELECT SUM(percentComplete * metricWeight)
FROM tabDeliverableStatus z1
INNER JOIN tabDeliverables y1
ON z1.deliverableID = y1.deliverableID
INNER JOIN tabMetricWeights x1
ON y1.weight = x1.metricWeightID
WHERE z1.deliverableID = a.deliverableID
AND z1.reportID = b.reportID) AS earnedValue
FROM tabDeliverables a
INNER JOIN tabDeliverableStatus b
ON a.deliverableID = b.deliverableID
INNER JOIN tabStatusReports c
ON b.reportID = c.statusReportID
INNER JOIN tabOrders d
ON c.orderID = d.orderID
INNER JOIN tabProjects e
ON d.project = e.projectID
WHERE e.program = @intAreaID –Insert job values into temp table
INSERT INTO #PlannedValue
SELECT
jobID,
(SELECT f1.project FROM tabJobs d1
INNER JOIN tabOrdersJobs e1
ON d1.jobID = e1.joinJobID
INNER JOIN tabOrders f1
ON e1.joinOrderID = f1.orderID
WHERE d1.jobID = a.jobID) AS projectID,
startDate,
endDate,
(SELECT ISNULL(SUM(quantity * price),0) FROM tabResources b1
INNER JOIN tabTasks c1 ON b1.taskID = c1.taskID
WHERE c1.job = a.jobID) AS dollars
FROM tabJobs a
INNER JOIN tabOrdersJobs e1
ON a.jobID = e1.joinJobID
INNER JOIN tabOrders f1
ON e1.joinOrderID = f1.orderID
INNER JOIN tabProjects g1
ON f1.project = g1.projectID
WHERE g1.program = @intAreaID
SET NOCOUNT OFF — Query tables to get EVM stats by area
SELECT
a.projectID AS fieldID,
a.projName AS fieldName,
(SELECT SUM(quantity * price) FROM tabResources b1
INNER JOIN tabTasks c1
ON b1.taskID = c1.taskID
INNER JOIN tabJobs d1
ON c1.job = d1.jobID
INNER JOIN tabOrdersJobs e1
ON d1.jobID = e1.joinJobID
INNER JOIN tabOrders f1
ON e1.joinOrderID = f1.orderID
WHERE f1.project = a.projectID) AS budget,
(SELECT SUM(a1.quantity * b1.price) FROM tabTaskStatus a1
INNER JOIN tabResources b1
ON a1.resourceID = b1.resourceID
INNER JOIN tabTasks c1
ON b1.taskID = c1.taskID
INNER JOIN tabJobs d1
ON c1.job = d1.jobID
INNER JOIN tabOrdersJobs e1
ON d1.jobID = e1.joinJobID
INNER JOIN tabOrders f1
ON e1.joinOrderID = f1.orderID
WHERE f1.project = a.projectID) AS statuscost,
(SELECT SUM(budget) FROM #EarnedValue
WHERE itemGroupID = a.projectID) AS earnedvalue,
(SELECT SUM(CASE
WHEN b.EndDt <= dbo.get_max_status_date(@intAreaID, ‘prog’) THEN ISNULL(b.budget,0)
WHEN b.StartDt >= dbo.get_max_status_date(@intAreaID, ‘prog’) THEN 0
ELSE (ISNULL(b.budget,0)/DATEDIFF(dy, b.StartDt, b.EndDt)) * DATEDIFF(dy, b.StartDt, dbo.get_max_status_date(@intAreaID, ‘prog’))
END) FROM #PlannedValue b
WHERE b.itemGroupID = a.projectID) AS currentplan FROM tabProjects a
WHERE a.program = @intAreaID
END
IF @strAreaView = ‘proj’
BEGIN
— Insert EVM amounts into table
INSERT INTO #EarnedValue
SELECT
a.deliverableID,
(SELECT e1.joinOrderID FROM tabDeliverables b1
INNER JOIN tabJobs d1
ON b1.jobID = d1.jobID
INNER JOIN tabOrdersJobs e1
ON d1.jobID = e1.joinJobID
WHERE b1.deliverableID = a.deliverableID) AS orderINT,
— Get total job value where this deliverable exists
(SELECT SUM(t1.quantity * price)
FROM tabResources t1
INNER JOIN tabTasks s1
ON t1.taskID = s1.taskID
INNER JOIN tabJobs r1
ON s1.job = r1.jobID
WHERE r1.jobID =
(SELECT jobID
FROM tabDeliverables
WHERE deliverableID = a.deliverableID))/
— Divide total job value by total weighted quantity of job deliverables
(SELECT SUM(w1.quantity * metricWeight)
FROM tabDeliverables w1
INNER JOIN tabJobs v1
ON w1.jobID = v1.jobID
INNER JOIN tabMetricWeights u1
ON w1.weight = u1.metricWeightID
WHERE v1.jobID =
(SELECT jobID
FROM tabDeliverables
WHERE deliverableID = a.deliverableID))*
— Multiply result by weighted completion amount to get EV
(SELECT SUM(percentComplete * metricWeight)
FROM tabDeliverableStatus z1
INNER JOIN tabDeliverables y1
ON z1.deliverableID = y1.deliverableID
INNER JOIN tabMetricWeights x1
ON y1.weight = x1.metricWeightID
WHERE z1.deliverableID = a.deliverableID
AND z1.reportID = b.reportID) AS earnedValue
FROM tabDeliverables a
INNER JOIN tabDeliverableStatus b
ON a.deliverableID = b.deliverableID
INNER JOIN tabStatusReports c
ON b.reportID = c.statusReportID
INNER JOIN tabOrders d
ON c.orderID = d.orderID
WHERE d.project = @intAreaID –Insert job values into temp table
INSERT INTO #PlannedValue
SELECT
jobID,
(SELECT e1.joinOrderID FROM tabJobs d1
INNER JOIN tabOrdersJobs e1
ON d1.jobID = e1.joinJobID
WHERE d1.jobID = a.jobID) AS orderID,
startDate,
endDate,
(SELECT SUM(quantity * price) FROM tabResources b1
INNER JOIN tabTasks c1 ON b1.taskID = c1.taskID
WHERE c1.job = a.jobID) AS dollars
FROM tabJobs a
INNER JOIN tabOrdersJobs e1
ON a.jobID = e1.joinJobID
INNER JOIN tabOrders f1
ON e1.joinOrderID = f1.orderID
WHERE f1.project = @intAreaID
SET NOCOUNT OFF — Query tables to get EVM stats by area
SET ANSI_NULLS OFF –nk
SELECT
a.orderID AS fieldID,
a.orderNum + ‘ ‘ + a.orderTitle AS fieldName,
(SELECT SUM(quantity * price) FROM tabResources b1
INNER JOIN tabTasks c1
ON b1.taskID = c1.taskID
INNER JOIN tabJobs d1
ON c1.job = d1.jobID
INNER JOIN tabOrdersJobs e1
ON d1.jobID = e1.joinJobID
WHERE e1.joinOrderID = a.orderID) AS budget,
(SELECT SUM(a1.quantity * b1.price) FROM tabTaskStatus a1
INNER JOIN tabResources b1
ON a1.resourceID = b1.resourceID
INNER JOIN tabTasks c1
ON b1.taskID = c1.taskID
INNER JOIN tabJobs d1
ON c1.job = d1.jobID
INNER JOIN tabOrdersJobs e1
ON d1.jobID = e1.joinJobID
WHERE e1.joinOrderID = a.orderID) AS statuscost,
(SELECT SUM(budget) FROM #EarnedValue
WHERE itemGroupID = a.orderID) AS earnedvalue,
(SELECT SUM(CASE
WHEN b.EndDt <= dbo.get_max_status_date(@intAreaID, ‘proj’) THEN b.budget
WHEN b.StartDt >= dbo.get_max_status_date(@intAreaID, ‘proj’) THEN 0
ELSE (b.budget/DATEDIFF(dy, b.StartDt, b.EndDt)) * DATEDIFF(dy, b.StartDt, dbo.get_max_status_date(@intAreaID, ‘proj’))
END) FROM #PlannedValue b
WHERE b.itemGroupID = a.orderID) AS currentplan FROM tabOrders a
WHERE a.project = @intAreaID
END
SET ANSI_NULLS ON –nk
IF @strAreaView = ‘ordr’
BEGIN
— Insert EVM amounts into table
INSERT INTO #EarnedValue
SELECT
a.deliverableID,
a.jobID,
— Get total job value where this deliverable exists
(SELECT SUM(t1.quantity * price)
FROM tabResources t1
INNER JOIN tabTasks s1
ON t1.taskID = s1.taskID
INNER JOIN tabJobs r1
ON s1.job = r1.jobID
WHERE r1.jobID =
(SELECT jobID
FROM tabDeliverables
WHERE deliverableID = a.deliverableID))/
— Divide total job value by total weighted quantity of job deliverables
(SELECT SUM(w1.quantity * metricWeight)
FROM tabDeliverables w1
INNER JOIN tabJobs v1
ON w1.jobID = v1.jobID
INNER JOIN tabMetricWeights u1
ON w1.weight = u1.metricWeightID
WHERE v1.jobID =
(SELECT jobID
FROM tabDeliverables
WHERE deliverableID = a.deliverableID))*
— Multiply result by weighted completion amount to get EV
(SELECT SUM(percentComplete * metricWeight)
FROM tabDeliverableStatus z1
INNER JOIN tabDeliverables y1
ON z1.deliverableID = y1.deliverableID
INNER JOIN tabMetricWeights x1
ON y1.weight = x1.metricWeightID
WHERE z1.deliverableID = a.deliverableID
AND z1.reportID = b.reportID) AS earnedValue
FROM tabDeliverables a
INNER JOIN tabDeliverableStatus b
ON a.deliverableID = b.deliverableID
INNER JOIN tabStatusReports c
ON b.reportID = c.statusReportID
WHERE c.orderID = @intAreaID –Insert job values into temp table
INSERT INTO #PlannedValue
SELECT
jobID,
jobID,
startDate,
endDate,
(SELECT SUM(quantity * price) FROM tabResources b1
INNER JOIN tabTasks c1 ON b1.taskID = c1.taskID
WHERE c1.job = a.jobID) AS dollars
FROM tabJobs a
INNER JOIN tabOrdersJobs e1
ON a.jobID = e1.joinJobID
WHERE e1.joinOrderID = @intAreaID
SET NOCOUNT OFF — Query tables to get EVM stats by area
SELECT
a.jobID AS fieldID,
a.jobTitle AS fieldName,
(SELECT SUM(quantity * price) FROM tabResources b1
INNER JOIN tabTasks c1
ON b1.taskID = c1.taskID
INNER JOIN tabJobs d1
ON c1.job = d1.jobID
WHERE d1.jobID = a.jobID) AS budget,
(SELECT SUM(a1.quantity * b1.price) FROM tabTaskStatus a1
INNER JOIN tabResources b1
ON a1.resourceID = b1.resourceID
INNER JOIN tabTasks c1
ON b1.taskID = c1.taskID
INNER JOIN tabJobs d1
ON c1.job = d1.jobID
WHERE d1.jobID = a.jobID) AS statuscost,
(SELECT SUM(budget) FROM #EarnedValue
WHERE itemGroupID = a.jobID) AS earnedvalue,
(SELECT SUM(CASE
WHEN b.EndDt <= dbo.get_max_status_date(@intAreaID, ‘ordr’) THEN b.budget
WHEN b.StartDt >= dbo.get_max_status_date(@intAreaID, ‘ordr’) THEN 0
ELSE (b.budget/DATEDIFF(dy, b.StartDt, b.EndDt)) * DATEDIFF(dy, b.StartDt, dbo.get_max_status_date(@intAreaID, ‘ordr’))
END) FROM #PlannedValue b
WHERE b.itemGroupID = a.jobID) AS currentplan FROM tabJobs a
INNER JOIN tabOrdersJobs AA1
ON a.jobID = AA1.joinJobID
WHERE AA1.joinOrderID = @intAreaID
END DROP TABLE #EarnedValue
DROP TABLE #PlannedValue
go
Having re-read Chappy’s first reply, I apologize for not checking into that more closely at the time. My problem was that I was looking for an ANSI setting in the Query Analyzer menu/toolbar options instead of looking through the Books Online to read up on ANSI_WARNINGS. I will examine your posts much more thoroughly in the future, and thanks for your help!!
]]>