May 30, 2012 at 12:05 pm
How do you do something like this? I want to replace SUM(X.A)/SUM(X.B) with FX3.Val3
CREATE FUNCTION [dbo].[itvfCalc](@Val1 INT, @Val2 INT)
RETURNS TABLE As
RETURN SELECT @Val1 * @Val2 As Val3
CREATE FUNCTION [dbo].[itvfCalc2](@Val1 INT, @Val2 INT)
RETURNS TABLE As
RETURN SELECT @Val1 + @Val2 As Val3
CREATE FUNCTION [dbo].[itvfCalc3](@Val1 INT, @Val2 INT)
RETURNS TABLE As
RETURN SELECT @Val1/@Val2 As Val3
SELECT SUM(X.A)/SUM(X.B)
FROM (
SELECT TOP 10 FX.Val3 AS A, FX2.Val3 B
FROM [AdventureWorks].[Production].[Product] P
INNER JOIN [AdventureWorks].[Production].[ProductCostHistory] PH ON P.ProductID = PH.ProductID
CROSS APPLY dbo.itvfCalc(P.ProductID,PH.ProductID) FX
CROSS APPLY dbo.itvfCalc2(P.ProductID,PH.ProductID) FX2
) X
I get the error "Aggregates on the right side of an APPLY cannot reference columns from the left side." when I try this.
SELECT FX3.Val3
FROM (
SELECT TOP 10 FX.Val3 AS A, FX2.Val3 B
FROM [AdventureWorks].[Production].[Product] P
INNER JOIN [AdventureWorks].[Production].[ProductCostHistory] PH ON P.ProductID = PH.ProductID
CROSS APPLY dbo.itvfCalc(P.ProductID,PH.ProductID) FX
CROSS APPLY dbo.itvfCalc2(P.ProductID,PH.ProductID) FX2
) X
CROSS APPLY dbo.itvfCalc3(SUM(X.A),SUM(X.B)) FX3
May 30, 2012 at 12:31 pm
Use a CTE or derived table.
The CROSS APPLY isn't going to give you what you expect anyhow--which is probably why MS put in that error message in the first place. Remember the CROSS APPLY runs the function for each row in the dataset. That means that any aggregate that references a column from the left side will be aggregating over the single value from the current row. While you can aggregate over a single value, there's not much point in doing so, especially if you can only ever have a single value.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply