Hi, I want to work with UNION option on SQL. The first part works fine, but when I added the Second part (after union) I got error message: The multi-part identifier "dbo.BFI_BOL_PickDetails_1.Customer" could not be found, and the same message for all of the fields are being used. I want to make the union because I want to get "1" for all of the fields in the second part.
How can I fix it? Thanks for help
SELECT dbo.BFI_BOL_PickDetails.StockCode, dbo.BFI_BOL_PickDetails.Document_Ref, dbo.BFI_BOL_PickDetails.Bin, dbo.BFI_BOL_PickDetails.Proximity, dbo.BFI_BOL_PickDetails.Item_Qty, dbo.BFI_BOL_PickDetails.CustStockCode, dbo.ArCustomer.MasterAccount, dbo.v_BFI_ArCustomer_StockXRef.Department, dbo.v_BFI_ArCustomer_StockXRef.SKU, dbo.v_BFI_ArCustomer_StockXRef.DrawingNumber, dbo.v_BFI_ArCustomer_StockXRef.UPC, ISNULL(dbo.v_Lbl_SalesOrder_SorContract_BG1_RetailPrices.CustomerListingRetailPrice, 0) AS ContractRetailPrice FROM dbo.BFI_BOL_PickDetails INNER JOIN dbo.ArCustomer ON dbo.BFI_BOL_PickDetails.Customer = dbo.ArCustomer.Customer INNER JOIN dbo.v_BFI_ArCustomer_StockXRef ON dbo.ArCustomer.MasterAccount = dbo.v_BFI_ArCustomer_StockXRef.MasterAccount AND dbo.BFI_BOL_PickDetails.StockCode = dbo.v_BFI_ArCustomer_StockXRef.StockCode INNER JOIN dbo.v_Lbl_SalesOrder_SorContract_BG1_RetailPrices ON dbo.BFI_BOL_PickDetails.Document_Ref = dbo.v_Lbl_SalesOrder_SorContract_BG1_RetailPrices.SalesOrder AND dbo.BFI_BOL_PickDetails.StockCode = dbo.v_Lbl_SalesOrder_SorContract_BG1_RetailPrices.MStockCode UNION SELECT 1 AS StockCode, 1 AS Document_Ref, 0 AS Bin, 9999999 AS Proximity, 1 AS Item_Qty, 1 AS CustStockCode, ArCustomer_1.MasterAccount, 1 AS Department, 1 AS SKU, 1 AS DrawingNumber, 1 AS UPC, ISNULL(dbo.v_Lbl_SalesOrder_SorContract_BG1_RetailPrices.CustomerListingRetailPrice, 0) AS ContractRetailPrice FROM dbo.BFI_BOL_PickDetails AS BFI_BOL_PickDetails_1 INNER JOIN dbo.ArCustomer AS ArCustomer_1 ON dbo.BFI_BOL_PickDetails_1.Customer = dbo.ArCustomer.Customer INNER JOIN dbo.v_BFI_ArCustomer_StockXRef AS v_BFI_ArCustomer_StockXRef_1 ON dbo.ArCustomer_1.MasterAccount = dbo.v_BFI_ArCustomer_StockXRef.MasterAccount AND dbo.BFI_BOL_PickDetails_1.StockCode = dbo.v_BFI_ArCustomer_StockXRef.StockCode INNER JOIN dbo.v_Lbl_SalesOrder_SorContract_BG1_RetailPrices AS v_Lbl_SalesOrder_SorContract_BG1_1 ON dbo.BFI_BOL_PickDetails_1.Document_Ref = dbo.v_Lbl_SalesOrder_SorContract_BG1_RetailPrices.SalesOrder AND dbo.BFI_BOL_PickDetails_1.StockCode = dbo.v_Lbl_SalesOrder_SorContract_BG1_RetailPrices.MStockCode GROUP BY ArCustomer_1.Customer, ArCustomer_1.MasterAccount, BFI_BOL_PickDetails_1.Document_Ref
Answer by RickD ·
You need to learn to Alias properly. The following is much easier to read and see where you went wrong.
SELECT PD.StockCode, PD.Document_Ref, PD.Bin, PD.Proximity, PD.Item_Qty, PD.CustStockCode, AC.MasterAccount, vSR.Department, vSR.SKU, vSR.DrawingNumber, vSR.UPC, ISNULL(vRP.CustomerListingRetailPrice, 0) AS ContractRetailPrice FROM dbo.BFI_BOL_PickDetails PD INNER JOIN dbo.ArCustomer AC ON PD.Customer = AC.Customer INNER JOIN dbo.v_BFI_ArCustomer_StockXRef vSR ON AC.MasterAccount = vSR.MasterAccount AND PD.StockCode = vSR.StockCode INNER JOIN dbo.v_Lbl_SalesOrder_SorContract_BG1_RetailPrices vRP ON PD.Document_Ref = vRP.SalesOrder AND PD.StockCode = vRP.MStockCode UNION SELECT 1 AS StockCode, 1 AS Document_Ref, 0 AS Bin, 9999999 AS Proximity, 1 AS Item_Qty, 1 AS CustStockCode, AC1.MasterAccount, 1 AS Department, 1 AS SKU, 1 AS DrawingNumber, 1 AS UPC, ISNULL(vRP1.CustomerListingRetailPrice, 0) AS ContractRetailPrice FROM dbo.BFI_BOL_PickDetails AS PD1 INNER JOIN dbo.ArCustomer AS AC1 ON PD1.Customer = AC1.Customer INNER JOIN dbo.v_BFI_ArCustomer_StockXRef AS vSR1 ON AC1.MasterAccount = vSR1.MasterAccount AND PD1.StockCode = vSR1.StockCode INNER JOIN dbo.v_Lbl_SalesOrder_SorContract_BG1_RetailPrices AS vRP1 ON PD1.Document_Ref = vRP1.SalesOrder AND PD1.StockCode = vRP1.MStockCode GROUP BY AC1.Customer, AC1.MasterAccount, PD1.Document_Ref
I had to do this just to be able to read the code without my eyes trying to tear themselves away from the screen.
You went wrong in your union with your joins not using the Aiases:
INNER JOIN dbo.ArCustomer AS ArCustomer_1 ON dbo.BFI_BOL_PickDetails_1.Customer = dbo.ArCustomer.Customer
Will not work as the join does not know what dbo.ArCustomer table is as you have called it ArCustomer_1 by using the alias.