CODEWITHSUNDEEP
sqlsql-server-2008
franglais
franglais

Reputation: 938

Multiple columns in aggregated expression

I've been struggling with an elegant solution for this for a while, and thought I'd finally cracked it but am now getting the error

Multiple columns are specified in an aggregated expression containing an outer reference. If an expression being aggregated contains an outer reference, then that outer reference must be the only column referenced in the expression.

Which is frustrating me!

In essence the query is:

select 
u.username + ' ' + u.surname,
CASE WHEN ugt.type = 'Contract'
  THEN
    (
      select sum(dbo.GET_INVOICE_WEEKLY_AVERAGE_VALUE(pc.placementid, u.UserId)) 
                from PlacementConsultants pc
                where pc.UserId = u.UserId
                and pc.CommissionPerc >= 80
    )
  END
from usergradetypes ugt
inner join usergrades ug on ug.gradeid = ugt.usergradetypeid
inner join users u on u.userid = ug.userid

The function GET_INVOICE_WEEKLY_AVERAGE_VALUE is as follows

ALTER function [dbo].[GET_INVOICE_WEEKLY_AVERAGE_VALUE]( @placementid INT, @userid INT )
RETURNS numeric(9,2)
AS
BEGIN

    DECLARE @retVal numeric(9,2)
    DECLARE @rollingweeks int

    SET @rollingweeks = (select ISNULL(rollingweeks,26) FROM UserGradeTypes ugt 
                            inner join UserGrades ug on ugt.UserGradeTypeID = ug.gradeid
                            WHERE ug.userid = @userid)
    SELECT @retVal =
                 sum(dbo.GET_INVOICE_NET_VALUE(id.InvoiceId)) / @rollingweeks from PlacementInvoices pli 
inner join invoicedetails id on id.invoiceid = pli.InvoiceId
where pli.PlacementID = @placementid
and pli.CreatedOn between DATEADD(ww,-@rollingweeks,getdate()) and GETDATE()


RETURN @retVal

The query runs fine without the sum but when I'm trying to sum the value of the deals, it's falling over (which I need to do for a summary page)

Upvotes: 0

Views: 1665

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

I do not know why this fails:

select sum(dbo.GET_INVOICE_WEEKLY_AVERAGE_VALUE(pc.placementid, u.UserId))

but this works:

select sum(dbo.GET_INVOICE_WEEKLY_AVERAGE_VALUE(pc.placementid, pc.UserId))

It is curious and seems like a bug to me.

The error message, though, suggests that all the columns inside the sum() need to come from either the outer referenced tables or the inner referenced tables, but not both. I don't understand the reason for this. My best guess is that mixing the two types of references confuses the optimizer.

I haven't seen this error message before, by the way.

EDIT:

It is very easy to reproduce, and does not require a function call:

with t as (select 1 as col)
    select t.*,
           (select sum(t2.col + t.col) from t t2) as newcol
    from t;

Very interesting. I think this might violate the standard. The equivalent query does run on Oracle.

Upvotes: 2

Related Questions