Outer Reference Error When Using Multiple Outer References

Question

So I have this Query that was working well, but I needed to use the Factory Std Cost in a few other areas, so instead of calling the function multiple times I put it in an Outer Apply. However it doesn't seem to be working now. I'm getting 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.

Here is the working query:

SELECT
    DISTINCT ap.[SourcePartID] AS [Assembly Part ID],
    p.[PART_X] AS [Assembly Part #],
    p.[DESCR_X] AS [Assembly Part Description],
    oa2.[Part Count],
    oa2.[Total # of Parts],
    ([dbo].[fn_getFactoryStdCost](ap.[SourcePartID])) AS [Factory Std Cost],
    oa2.[# of Docs],
    oa2.[# of Software],
    'Logic Pending' AS [# of Std Cost Items],
    oa2.[# of HR Devices],
    oa2.[# of 3rd Party Devices],
    oa2.[# of Robots],
    oa2.[# of Buy Parts],
    oa2.[# of Make Parts]

  FROM AllPartsList ap
    LEFT JOIN visuser.EN_PART p
      ON p.[EN_Part_ID] = ap.[SourcePartID]
    OUTER APPLY (
        SELECT
            [Part Count]                = COUNT(    DISTINCT IIF( [Qty] = 0, null, [Component Part #])  ),  
            [Total # of Parts]          = SUM([Qty]),
            [# of Docs]                 = COUNT(    DISTINCT IIF( [Commodity Code] IN ('009', '072', '073', '075', '079', '082'), [Component Part #], null) ),  -- Commodity Codes: 009, 072, 073, 075, 079, 082  :  Commodity ID: 15, 84, 85, 87, 81, 92
            [# of Software]             = COUNT(    DISTINCT IIF( [Commodity Code] IN ('034'), [Component Part #], null)    ),                                  -- Commodity Code 034  :  Commodity ID: 28
            [# of HR Devices]           = COUNT(    DISTINCT IIF( [Commodity Code] IN ('002'), [Component Part #], null)    ),                                  -- Commodity Code 002  :  Commodity ID: 11
            [# of 3rd Party Devices]    = COUNT(    DISTINCT IIF( [Commodity Code] IN ('007'), [Component Part #], null)    ),                                  -- Commodity Code 007  :  Commodity ID: 5
            [# of Robots]               = COUNT(    DISTINCT IIF( ( [Commodity Code] IN ('005') /* AND [Make/Buy] = 'B' */ ), [Component Part #], null) )   ,       -- Commodity Code 005  :  Commodity ID: 13
            [# of Make Parts]           = COUNT(    DISTINCT IIF( [Make/Buy] = 'M', [Component Part #], null)   ),
            [# of Buy Parts]            = COUNT(    DISTINCT IIF( [Make/Buy] = 'B', [Component Part #], null)   ),
            [# of Ref Parts]            = COUNT(    DISTINCT IIF( [Make/Buy] = 'B', [Component Part #], null)   )
            
          FROM bomBreakdown
          WHERE
            [ComponentPartID] IS NOT NULL AND 
            [SourcePartID] = ap.[SourcePartID]
          GROUP BY [SourcePartID]
    ) oa2
    ORDER BY [PART_X]

Here is what I changed it to. I moved the call to the function to the an Outer Apply, and used it in the main query as well as the second Outer Apply. The error references the first line of the second Outer Apply with the oa1.[Factory Std Cost]

SELECT
    DISTINCT ap.[SourcePartID] AS [Assembly Part ID],
    p.[PART_X] AS [Assembly Part #],
    p.[DESCR_X] AS [Assembly Part Description],
    oa2.[Part Count],
    oa2.[Total # of Parts],
    oa1.[Factory Std Cost], 
    oa2.[# of Docs],
    oa2.[# of Software],
    'Logic Pending' AS [# of Std Cost Items],
    oa2.[# of HR Devices],
    oa2.[# of 3rd Party Devices],
    oa2.[# of Robots],
    oa2.[# of Buy Parts],
    oa2.[# of Make Parts]

  FROM AllPartsList ap
    LEFT JOIN visuser.EN_PART p
      ON p.[EN_Part_ID] = ap.[SourcePartID]
    OUTER APPLY (
      SELECT ([dbo].[fn_getFactoryStdCost](ap.[SourcePartID])) AS [Factory Std Cost]
    ) oa1
    OUTER APPLY (
        SELECT
            [Part Count]                = COUNT(    DISTINCT IIF( [Qty] = 0, null, [Component Part #])  ),  
            [Total # of Parts]          = SUM([Qty]),
            [# of Docs]                 = COUNT(    DISTINCT IIF( [Commodity Code] IN ('009', '072', '073', '075', '079', '082'), [Component Part #], null) ),  -- Commodity Codes: 009, 072, 073, 075, 079, 082  :  Commodity ID: 15, 84, 85, 87, 81, 92
            [# of Software]             = COUNT(    DISTINCT IIF( [Commodity Code] IN ('034'), [Component Part #], null)    ),                                  -- Commodity Code 034  :  Commodity ID: 28
            [# of HR Devices]           = COUNT(    DISTINCT IIF( ( [Commodity Code] IN ('002') AND oa1.[Factory Std Cost] > 0 ), [Component Part #], null) ),                                  -- Commodity Code 002  :  Commodity ID: 11
            [# of 3rd Party Devices]    = COUNT(    DISTINCT IIF( [Commodity Code] IN ('007'), [Component Part #], null)    ),                                  -- Commodity Code 007  :  Commodity ID: 5
            [# of Robots]               = COUNT(    DISTINCT IIF( ( [Commodity Code] IN ('005') /* AND [Make/Buy] = 'B' */ ), [Component Part #], null) )   ,       -- Commodity Code 005  :  Commodity ID: 13
            [# of Make Parts]           = COUNT(    DISTINCT IIF( ( [Make/Buy] = 'M' AND oa1.[Factory Std Cost] > 0 ), [Component Part #], null)    ),
            [# of Buy Parts]            = COUNT(    DISTINCT IIF( ( [Make/Buy] = 'B' AND oa1.[Factory Std Cost] > 0 ), [Component Part #], null)    ),
            [# of Ref Parts]            = COUNT(    DISTINCT IIF( ( [Make/Buy] = 'B' AND oa1.[Factory Std Cost] = 0 ), [Component Part #], null)    )
            
          FROM bomBreakdown
          WHERE
            [ComponentPartID] IS NOT NULL AND 
            [SourcePartID] = ap.[SourcePartID]
          GROUP BY [SourcePartID]
    ) oa2
    ORDER BY [PART_X]

Here's what the AllPartsList looks like:

And bomBreakdown:

I reverted the query and then tried adding things a bit at a time to see exactly where it went wrong. I added the line for [# of Ref Parts] with only the Make/But = 'B' (it's basically identical to the Buy Parts). It worked fine until I added the function for the stdCost. I then received the same outer reference error. Here is the one line:

COUNT(  DISTINCT IIF( ( [Make/Buy] = 'B' AND ([dbo].[fn_getFactoryStdCost](ap.[SourcePartID])) = 0 ), [Component Part #], null) )

After some playing around, I found that the issue is the ap.[SourcePartID] in the call to the function. That's a key field to link the AllPartsList and the bomBreakdown tables, so I can remove the ap. and just use the one in the bomBreakdown table, but it doesn't address the issues I was trying to solve in the first place of not calling the function multiple times.

I'm using:

SQL Server 2019 (v15.0.2070.41)
SSMS v18.5

First, I could imagine that you can simplify the query to focus on the problem you are having. Second, without qualified column names, the query is pretty much impenetrable. What columns are coming from which tables? — Gordon Linoff, Jul 28 '20 at 00:44
@GordonLinoff I don't know what you're talking about. All the tables have aliases that I use with the columns. AllPartsList = ap, EN_PART = p, Outer Apply 1 = oa1, Outer Apply 2 = oa2. The only place I see that I don't really use them is in oa2, but bomBreakdown is the only table in the query... — Dizzy49, Jul 28 '20 at 02:43
Does this answer your question? [Error: "Multiple columns are specified in an aggregated expression containing an outer reference."](https://stackoverflow.com/questions/26534330/error-multiple-columns-are-specified-in-an-aggregated-expression-containing-an) — Wouter, Jul 28 '20 at 07:34
@Dizzy49 . . . Qualified *column names*, not table aliases. Those are quite different. It is entirely unclear where the columns come from. — Gordon Linoff, Jul 28 '20 at 10:48
@GordonLinoff They are not quite different. The aliases tell you exactly where the columns are coming from. `ap.[SourcePartID]` is the `SourcePartId` column of the `AllPartsList` table. I even included a snapshot of the table for reference. — Dizzy49, Jul 28 '20 at 17:20
@Wouter Unfortunately not, and the link to the detailed explanation in the article is dead. — Dizzy49, Jul 28 '20 at 17:32

score 1 · Accepted Answer · answered Jul 28 '20 at 07:33

1

You can't reference an OUTER column inside an aggregation as the error message suggests. You can try it on a small example yourself and you'll see that MS SQL does not allow that. Here is one idea where the outer reference was turned into a inner reference via additional JOIN inside the OUTER sub query

Error: "Multiple columns are specified in an aggregated expression containing an outer reference."

answered Jul 28 '20 at 07:33

K4M

1,030
3
11

Thanks, I didn't quite follow everything in the article, but 'via additional JOIN inside the OUTER sub query' got me in the right direction. I was hoping to only call the function once for the entire query (vs 5 times), but I added a JOIN in the OUTER and I just call the function once instead of 4 times, and I still have to call the function in the main query. – Dizzy49 Jul 28 '20 at 17:33

Outer Reference Error When Using Multiple Outer References

1 Answers1