MERGE语句含CASE查询报错：未指定SOURCE列名

错误根源 #

报错“No column name was specified for column ... of SOURCE”的核心原因是SELECT子查询中的两个CASE表达式未定义列别名，导致SOURCE数据集无法识别这些列；同时代码存在笔误和字段映射不匹配问题。

修正方案 #

• 为SELECT子查询中的两个CASE表达式分别添加列别名AS SSthick和AS PanelType，确保SOURCE数据集能正确引用这些计算列
• 修正VALUES子句中的笔误：
  • 将Source.Source.ExtendedPrice改为Source.ExtendedPrice
  • 将SSource.hutterComments改为Source.ShutterComments
• 调整INSERT列与SOURCE字段的对应关系：
  • CRP1Req对应Source.CRP1
  • CRP2Req对应Source.CRP2
  • CRP3Req对应Source.CRP3

修正后的完整代码 #

MERGE OrderDetails AS TARGET
USING
(
SELECT OrderHeader.OrderNo, SalesOrderData.[LineNo], SalesOrderData.WebOrderNo, SalesOrderData.quantity,ISNULL(SalesOrderData.BasePrice,'0') AS BasePrice,ISNULL(SalesOrderData.CustomPrice,'0') AS CustomPrice,
ISNULL(SalesOrderData.FinishingPrice,'0')AS FinishingPrice,ISNULL(SalesOrderData.NonDiscount2,'0') AS NonDiscountable, ISNULL(SalesOrderData.TotalPrice,'0') AS TotalPrice,
ISNULL(SalesOrderData.ExtendedPrice,'0') As ExtendedPrice,SalesOrderData.Category,SalesOrderData.StockNo,SalesOrderData.SSD,SalesOrderData.VerticalSS,SalesOrderData.LorH, SalesOrderData.WorB, SalesOrderData.Species,
SalesOrderData.FinishingOption, SalesOrderData.Grade, SalesOrderData.Sheen, SalesOrderData.FinishingBrand,SalesOrderData.ColorName,SalesOrderData.ColorNo,SalesOrderData.StockPaint,                        
     CASE 
      WHEN SalesOrderData.Category = 'TL' THEN '1.125'
      WHEN SalesOrderData.Category = 'RP' THEN '1.125'
      WHEN SalesOrderData.Category = 'CTL' THEN '1.25'
      WHEN SalesOrderData.Category = 'CTL-BAH' THEN '1.25'
      WHEN SalesOrderData.Category = 'CRP' THEN '1.25'
      WHEN SalesOrderData.Category = 'IRP' THEN '1.07'
      Else SalesOrderData.[SS Thickness]
    END AS SSthick,
SalesOrderData.BRD, SalesOrderData.TRD, SalesOrderData.CRD, SalesOrderData.CRD2, SalesOrderData.CRD3, SalesOrderData.CRP1,SalesOrderData.CRP2, SalesOrderData.CRP3, SalesOrderData.Section1, SalesOrderData.Section2,
SalesOrderData.Section3, SalesOrderData.Section4, 
    CASE
      WHEN SalesOrderData.PanelType = 'NA' THEN '-'
    Else SalesOrderData.PanelType
    END AS PanelType

FROM SalesOrderData
INNER JOIN OrderHeader ON SalesOrderData.WebOrderNo = OrderHeader.WebOrderNo 
INNER JOIN OrderDetails ON SalesOrderData.WebOrderNo = OrderDetails.WebOrderNo)
                        
AS SOURCE   
ON Source.WebOrderNo = Target.WebOrderNo        
WHEN NOT MATCHED BY TARGET
THEN 
INSERT 
(OrderNo, [LineNo], WebOrderNo, Quantity,BasePrice, CustomPrice, FinishingPrice, NonDiscountable,TotalPrice,ExtendedPrice, Category,StockNo, SSD, VerticalSS,LorH, WorB, Species,FinishingOption, Grade, Sheen, 
FinishingBrand, ColorName,ColorNo,StockPaint,SSthick, BRD, TRD, CRD, CRD2, CRD3, CRP1Req,CRP2Req, CRP3Req, Section1, Section2, Section3, Section4,PanelType, RailConfig, ItemText6,SStype, ArchLow, ArchHigh,
LouverDirection, TrimType, TrimLouverDirection,MHLouverDirection, FCRLouverDirection,MHTR,MHCR1,MHCR2,MHCR3,UM,VBoardWidth, VBoardTotal, HBoardWidth,VBoardSpacing,ShutterComments,Custom1,Custom2,Custom3,Custom4,Custom5)
VALUES 
(Source.OrderNo,Source.[LineNo], Source.WebOrderNo, Source.Quantity,Source.BasePrice, Source.CustomPrice, Source.FinishingPrice, Source.NonDiscountable,Source.TotalPrice,Source.ExtendedPrice, Source.Category,
Source.StockNo, Source.SSD, Source.VerticalSS,Source.LorH, Source.WorB, Source.Species, Source.FinishingOption, Source.Grade, Source.Sheen, Source.FinishingBrand, Source.ColorName,Source.ColorNo,Source.StockPaint,
Source.SSthick, Source.BRD, Source.TRD, Source.CRD, Source.CRD2, Source.CRD3, Source.CRP1,Source.CRP2, Source.CRP3, Source.Section1, Source.Section2, Source.Section3, Source.Section4,Source.PanelType,
Source.RailConfig, Source.ItemText6,Source.SStype, Source.ArchLow, Source.ArchHigh, Source.LouverDirection, Source.TrimType, Source.TrimLouverDirection,Source.MHLouverDirection, Source.FCRLouverDirection,Source.MHTR,
Source.MHCR1,Source.MHCR2,Source.MHCR3,Source.UM,Source.VBoardWidth, Source.VBoardTotal, Source.HBoardWidth,Source.VBoardSpacing,Source.ShutterComments,Source.Custom1,Source.Custom2,Source.Custom3,Source.Custom4,
Source.Custom5);

内容的提问来源于stack exchange，提问作者Pattie