I have a fairly complex Stored Procedure that is joining several tables together, but I need yet another column called in from a table that is yet to be joined.
Here's the Stored Procedure as it stands:
CREATE PROCEDURE [rpt].[PlannerShipToLocations_ds1]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql nvarchar(4000)
,@DateStart nvarchar(10) = '2015-01-01'
DECLARE @t TABLE (
[PCN] int
,[Part_Key] int
,[Customer_Address_No] int
PRIMARY KEY CLUSTERED (
[Part_Key]
,[Customer_Address_No]
,[PCN]
)
)
SET @sql =
'SELECT *
FROM OPENQUERY (PLEXRPTSVR,
''SELECT DISTINCT
s.[PCN]
,sl.[Part_Key]
,s.[Customer_Address_No]
FROM [Sales_v_Shipper_e] AS s
INNER JOIN [Sales_v_Shipper_Status_e] AS ss
ON s.[PCN] = ss.[PCN]
AND s.[Shipper_Status_Key] = ss.[Shipper_Status_Key]
INNER JOIN [Sales_v_Shipper_Line_e] AS sl
ON s.[PCN] = sl.[PCN]
AND s.[Shipper_Key] = sl.[Shipper_Key]
WHERE 1 = 1
AND ss.[Shipped] = 1
AND s.[Ship_Date] >= ''''' + @DateStart + '''''
;'')'
INSERT INTO @t
EXECUTE sp_executesql @sql
;WITH base AS (
SELECT DISTINCT u.[Last_Name] + ', ' + u.[First_Name] AS [Planner]
,c.[Customer_Code] AS [Customer Code]
,c.[Name] AS [Customer Name]
,a.[Customer_Address_Code] AS [Customer Address Code]
**/* xxx.[Country] AS [Country] */**
FROM [plx].[Part_v_Customer_Part_e] cp
INNER JOIN [plx].[Part_v_Part_e] p
ON cp.[Plexus_Customer_No] = p.[Plexus_Customer_No]
AND cp.[Part_Key] = p.[Part_Key]
INNER JOIN [plx].[Common_v_Customer_e] c
ON cp.[Plexus_Customer_No] = c.[Plexus_Customer_No]
AND cp.[Customer_No] = c.[Customer_No]
INNER JOIN [plx].[Plexus_Control_v_Plexus_User_e] u
ON p.[Plexus_Customer_No] = u.[Plexus_Customer_No]
AND p.[Planner] = u.[Plexus_User_No]
OUTER APPLY (
SELECT [Customer_Address_Code], **/*[Country]*/**
FROM [plx].[Common_v_Customer_Address_e] a
INNER JOIN @t t
ON a.[Plexus_Customer_No] = t.[PCN]
AND a.[Customer_Address_No] = t.[Customer_Address_No]
**/* INNER JOIN [plx].[Common_v_Country] xxx
ON a.[Country_Key] = xxx.[Country_Key] */**
WHERE a.[Plexus_Customer_No] = p.[Plexus_Customer_No]
AND a.[Customer_No] = c.[Customer_No]
AND t.[Part_Key] = p.[Part_Key]
AND a.[Ship_To] = 1
) a
**
/* INNER JOIN [plx].[Common_v_Country] xxx
ON a.[Country_Key] = xxx.[Country_Key] */
/*
OUTER APPLY (
SELECT [Country]
FROM [plx].[Common_v_Country] xxx
INNER JOIN @t t
ON a.[Country_Key] = xxx.[Country_Key]
WHERE a.[Plexus_Customer_No] = p.[Plexus_Customer_No]
AND a.[Customer_No] = c.[Customer_No]
AND t.[Part_Key] = p.[Part_Key]
AND a.[Ship_To] = 1
) xxx
*/**
WHERE 1 = 1
AND u.[Lockout] = 0
AND p.[Part_Status] IN ('Production', 'Production - Near EOP', 'Pre-Production')
AND cp.[Active] = 1
)
SELECT t1.[Planner]
,t1.[Customer Code]
,t1.[Customer Name]
,STUFF(
(SELECT
' | ' + t2.[Customer Address Code]
FROM base t2
WHERE t1.[Planner] = t2.[Planner]
and t1.[Customer Code] = t2.[Customer Code]
ORDER BY t2.[Customer Address Code]
FOR XML PATH(''), TYPE
).value('.','varchar(max)')
,1,3,'') AS [Customer Address Code(s)]
FROM base t1
GROUP BY t1.[Planner]
,t1.[Customer Code]
,t1.[Customer Name]
ORDER BY [Customer Code]
,[Planner]
,[Customer Address Code(s)]
END
GO
I've bolded the sections that are my best guesses about how to go about joining this additional table, I recognize that I wouldn't use all of them but I wanted to show my thoughts. To break it down:
1.) [plx].[Common_v_Customer_Address_e] a AND [plx].[Common_v_Country] xxx are the two tables I need in order to call [Country] out by name. I essentially need to add this as a column displayed on the report and eventually will need to sort on it as well (I'll probably add it to ORDER BY at the end).
2.) I'm not sure if I need to be joining [plx].[Common_v_Country] xxx within the OUTER APPLY or if it needs its own separate INNER JOIN or what. I've illustrated and commented out both here. So far everything I've tried results in "The multi-part identifier 'xxx.Country' could not be bound."
Thanks for the help.
I've tried modifying the OUTER APPLY to include the new table. I've tried creating my own new INNER JOIN. I've tried creating my own new OUTER APPLY.
