Query works in Analyzer but can't save it

Mitch · December 8th, 2003, 04:05 PM

I have the following UNION querey that works in SQL Query Anylyzer but does not work in Access 2002 or in Enterpise Manager. I am using SQL 2002

When I run it in Access as a View I get the error:
"ADO error: The column 'ParWOIDsub' was specified multiple times for view 'myViewName'"

When I run it from Enterprise Manager it runs, but if I try to save it it give me the error:
"View definition includes no output columns or includes no items in the FROM clause"

I don't understand it!
================================================== ===========

Code:

SELECT   TOP 100 PERCENT 
      [T_SetupSheetHistoryCombinationsDetail].QuoteID,
      [T_SetupSheetHistoryCombinationsDetail].WOID,
      [T_SetupSheetHistoryCombinationsDetail].WOIDSub,
      [T_SetupSheetHistoryCombinationsDetail].ECN,
      [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum,
      T_SetupSheetHistoryKit_QtyCalc.CompItemPartNum,
      T_SetupSheetHistoryKit_QtyCalc.CompQty,
      T_SetupSheetHistoryKit_QtyCalc.ParWOIDSub,
      vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder.QtyGoodPlusQtyInv,
    (
    CASE 
        WHEN ([QtyGoodPlusQtyInv]/[CompQty])=Null
            THEN 0
        ELSE ([QtyGoodPlusQtyInv]/[CompQty]) 
    END
    )AS KitQty,
       vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder.ParWOIDSub
FROM ([T_SetupSheetHistoryCombinationsDetail] 
INNER JOIN T_SetupSheetHistoryKit_QtyCalc 
     ON ([T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum = T_SetupSheetHistoryKit_QtyCalc.KitItemPartNum) 
     AND ([T_SetupSheetHistoryCombinationsDetail].ECN = T_SetupSheetHistoryKit_QtyCalc.ECN) 
     AND ([T_SetupSheetHistoryCombinationsDetail].WOIDSub = T_SetupSheetHistoryKit_QtyCalc.WOIDSub) 
     AND ([T_SetupSheetHistoryCombinationsDetail].WOID = T_SetupSheetHistoryKit_QtyCalc.WOID) 
     AND ([T_SetupSheetHistoryCombinationsDetail].QuoteID = T_SetupSheetHistoryKit_QtyCalc.QuoteID)) 
LEFT JOIN vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder 
     ON (T_SetupSheetHistoryKit_QtyCalc.ParWOIDSub = vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder.ParWOIDSub) 
     AND (T_SetupSheetHistoryKit_QtyCalc.WOID = vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder.WONum) 
     AND (T_SetupSheetHistoryKit_QtyCalc.CompItemPartNum = vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder.ComboCustPartNum)
WHERE (((vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder.ItemAppliesTo) = Null))

UNION 

SELECT  TOP 100 PERCENT 
      [T_SetupSheetHistoryCombinationsDetail].QuoteID,
      [T_SetupSheetHistoryCombinationsDetail].WOID,
      [T_SetupSheetHistoryCombinationsDetail].WOIDSub,
      [T_SetupSheetHistoryCombinationsDetail].ECN,
      [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum,
      T_SetupSheetHistoryKit_QtyCalc.CompItemPartNum,
      T_SetupSheetHistoryKit_QtyCalc.CompQty,
      T_SetupSheetHistoryKit_QtyCalc.ParWOIDSub,
      vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder.QtyGoodPlusQtyInv,
      (
    CASE 
        WHEN ([QtyGoodPlusQtyInv]/[CompQty])=Null
            THEN 0
        ELSE ([QtyGoodPlusQtyInv]/[CompQty]) 
    END
    )AS KitQty,

      vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder.ParWOIDSub
FROM ([T_SetupSheetHistoryCombinationsDetail] 
INNER JOIN T_SetupSheetHistoryKit_QtyCalc 
     ON ([T_SetupSheetHistoryCombinationsDetail].QuoteID = T_SetupSheetHistoryKit_QtyCalc.QuoteID) 
     AND ([T_SetupSheetHistoryCombinationsDetail].WOID = T_SetupSheetHistoryKit_QtyCalc.WOID) 
     AND ([T_SetupSheetHistoryCombinationsDetail].WOIDSub = T_SetupSheetHistoryKit_QtyCalc.WOIDSub) 
     AND ([T_SetupSheetHistoryCombinationsDetail].ECN = T_SetupSheetHistoryKit_QtyCalc.ECN) 
     AND ([T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum = T_SetupSheetHistoryKit_QtyCalc.KitItemPartNum)) 
LEFT JOIN vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder 
     ON (T_SetupSheetHistoryKit_QtyCalc.CompItemPartNum = vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder.ComboCustPartNum) 
     AND (T_SetupSheetHistoryKit_QtyCalc.WOID = vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder.WONum) 
     AND (T_SetupSheetHistoryKit_QtyCalc.ParWOIDSub = vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder.ParWOIDSub)
WHERE (((vSetupSheetHistoryCominationDetail_ItemQtyGoodPerOrder.ItemAppliesTo)=[T_SetupSheetHistoryCombinationsDetail].[ComboCustPartNum]))
ORDER BY [T_SetupSheetHistoryCombinationsDetail].WOID,
      [T_SetupSheetHistoryCombinationsDetail].ComboCustPartNum

defiant · December 10th, 2003, 12:48 PM

in a view you can't have two columns with thew same name. eliminate one of them or "rename" one of them like this:

...
vSetupSheetHistoryCominationDetail_ItemQtyGoodPerO rder.ParWOIDSub as ParWOIDSub2

defiant.

Mitch · December 15th, 2003, 01:22 PM

Yes Defiant, that worked!