Error - Agg expression containing outer reference...

[ddiamond]

Here is my original query:

UPDATE #AdjustedPremium
SET AdjustedNetPremium = c.NetPremium
FROM #AdjustedPremium a
  INNER JOIN AccountCurrent b ON a.AccountCurrentID = b.AccountCurrentID
  INNER JOIN #PremiumFinancialTemp c ON
        b.ProductCode              = 'CHIRO1'
    AND b.BookDate                 = '1/31/2010'
    AND b.Revised                  = 'N'
    AND b.Reference                = c.Reference
    AND a.AdjustedTransactionCode  = c.TransactionCode
    AND b.TransactionEffectiveDate = c.TransactionEffectiveDate
WHERE a.TimeProcessed IS NULL
  AND ABS(b.NetPremium - c.NetPremium) <= 5
  AND ABS(b.NetPremium - c.NetPremium) = (
    SELECT MIN(ABS(b.NetPremium - d.NetPremium))
    FROM #PremiumFinancialTemp d
    WHERE b.Reference                = d.Reference
      AND a.AdjustedTransactionCode  = d.TransactionCode
      AND b.TransactionEffectiveDate = d.TransactionEffectiveDate )

I get the following 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.

Well that is a strange limitation.

So I created a less efficient version of the query to get around the limitation:

UPDATE #AdjustedPremium
SET AdjustedNetPremium = c.NetPremium
FROM #AdjustedPremium a
  INNER JOIN AccountCurrent b ON a.AccountCurrentID = b.AccountCurrentID
  INNER JOIN #PremiumFinancialTemp c ON
        b.ProductCode              = 'CHIRO1'
    AND b.BookDate                 = '1/31/2010'
    AND b.Revised                  = 'N'
    AND b.Reference                = c.Reference
    AND a.AdjustedTransactionCode  = c.TransactionCode
    AND b.TransactionEffectiveDate = c.TransactionEffectiveDate
WHERE a.TimeProcessed IS NULL
  AND ABS(b.NetPremium - c.NetPremium) <= 5
  AND ABS(b.NetPremium - c.NetPremium) = (
    SELECT MIN(ABS(d.NetPremium - e.NetPremium))
    FROM AccountCurrent d
      INNER JOIN #PremiumFinancialTemp e ON
            d.Reference                = e.Reference
        AND a.AdjustedTransactionCode  = e.TransactionCode
        AND d.TransactionEffectiveDate = e.TransactionEffectiveDate
    WHERE d.AccountCurrentId = b.AccountCurrentId )

Whereas the 2nd query works, I wince at the redundancy. Does anyone have suggestions?

 

[genomon]

INNER JOIN #PremiumFinancialTemp c ON
b.ProductCode = 'CHIRO1'
AND b.BookDate = '1/31/2010'
AND b.Revised = 'N'

At first blush it seems these 3 items belong in the WHERE clause as they are not part of the join between the tables.

Not really addressing your issue here, just commenting...

Beir bua agus beannacht!

 

[SkipVought]

and maybe a similar issue in the ON clause...

(
    SELECT MIN(ABS(d.NetPremium - e.NetPremium))
    FROM AccountCurrent d
      INNER JOIN #PremiumFinancialTemp e ON
            d.Reference                = e.Reference[s]
        AND [b][red]a.AdjustedTransactionCode[/red][/b]  = e.TransactionCode[/s]
        AND d.TransactionEffectiveDate = e.TransactionEffectiveDate
    WHERE d.AccountCurrentId = b.AccountCurrentId )[b]
       AND a.AdjustedTransactionCode  = e.TransactionCode[/b]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[philhege]

SELECT MIN(ABS(b.NetPremium - d.NetPremium))

That's your problem. Do you understand why?

-------++NO CARRIER++-------

 

[ddiamond]

Philhege,

SELECT MIN(ABS(b.NetPremium - d.NetPremium))

I understand that is the problem because b.NetPremim refers to a field that is outside the sub-query and that is not allowed in an aggrate function. I don't understand why SQL Server does not allow this, but apparently it doesn't.

My 2nd solution solves this problem by joining to AccountCurrent for a 2nd time inside of the sub-query and then linking it to the accountcurrent in the outer query with the primary key AccountCurrentID. So there is a one to one relationship between the AccountCurrent in the outer query and the AccountCurrent in the inner query. I was just wondering if anyone can think of a better solution that doesn't involve this extra join.

genomon,

You are correct. Those three fields really belong in the where clause rather than the join clause, but you get the same results either way. In any case that is not what is causing the error.

 

[genomon]

but you get the same results either way

Sometimes you will be surprised how the query execution plans differ. Also, coding to standards makes code more readable, and easier to maintain for someone besides the author. Optimizing the query is problematic without knowing the structure of the tables ionvolved...

Beir bua agus beannacht!