如何解决使用OUTER APPLY和SELECT TOP(1)时的varchar转数值错误

错误原因分析 #

你遇到的Error converting data type varchar to numeric错误，本质是SQL查询优化器的执行顺序问题：虽然你在CTE里做了类型转换，但当使用TOP(1)和ORDER BY时，优化器可能会优先尝试转换所有Before字段的值，再进行排序和筛选，而日志表中存在无法转换为DECIMAL的非数值字符串，导致触发转换错误。单独跑CTE或单库存查询时，可能刚好没命中脏数据，所以没报错。

正确实现方案 #

核心思路是先安全转换字段，再筛选关联，同时确保每条发票只关联对应时点的成本：

1. 用TRY_CONVERT替代普通CONVERT，转换失败时返回NULL，避免报错；
2. 通过OUTER APPLY关联发票日期后第一条成本变更记录；
3. 无后续变更时， fallback到系统当前成本（假设当前成本存于物料主表StockMaster的CurrentCost字段，可根据实际表名调整）。

完整SQL代码 #

-- 假设发票表名为Invoice，修改日志表名为StockCostLog，物料主表名为StockMaster
SELECT
    i.StockCode,
    i.InvoiceDate,
    -- 优先取发票日期后第一条变更的Before值，无则用当前成本
    COALESCE(cost.FirstBeforeCost, sm.CurrentCost) AS CurrentCostAtInvoice
FROM Invoice i
LEFT JOIN StockMaster sm ON i.StockCode = sm.StockCode
OUTER APPLY (
    SELECT TOP(1)
        -- 安全转换Before字段，非数值返回NULL
        TRY_CONVERT(DECIMAL(18,2), sc.Before) AS FirstBeforeCost
    FROM StockCostLog sc
    WHERE sc.StockCode = i.StockCode
      AND sc.JnlDate > i.InvoiceDate
    -- 按变更日期升序，取第一条
    ORDER BY sc.JnlDate ASC
) cost

关键细节说明 #

• TRY_CONVERT(DECIMAL(18,2), sc.Before)：确保即使Before字段有非数值内容，也不会触发转换错误，而是返回NULL，后续通过COALESCE处理；
• OUTER APPLY + TOP(1) + ORDER BY sc.JnlDate ASC：精准获取发票日期之后的第一条成本变更记录；
• COALESCE：当没有后续变更记录时，自动使用物料主表的当前成本。

额外排查建议 #

如果仍有问题，可以先排查日志表中的脏数据：

-- 找出无法转换为DECIMAL的Before记录
SELECT StockCode, JnlDate, Before
FROM StockCostLog
WHERE TRY_CONVERT(DECIMAL(18,2), Before) IS NULL
  AND Before IS NOT NULL

清理或修正这些脏数据后，也可以换回普通CONVERT提升性能。

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