sqlsql-serverouter-apply

Outer Reference Error When Using Multiple Outer References

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: AllPartsList Table

And bomBreakdown: bomBreakdown Table

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:

Solution

  • 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."