SQL2005 UNION query

Question

question

jramirezj asked Oct 27, '09

SQL2005 UNION query

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

sql-server-2005 union

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2009-10-27T13:28:30Z

RickD answered Oct 27, '09

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question