列求和触发Operand错误Msg 8117：请求技术排查解决

Got it, let's break down this error and fix it step by step.

Why You're Getting This Error #

The Msg 8117 error is straightforward: you're trying to use the SUM() function on an nvarchar (text) field, and SUM only works with numeric data types like int, decimal, or float.

Looking at your query, one or more of these fields — T1.RecvQty, T0.Quantity, or T0.QuantOut — is stored as text instead of a numeric type. SQL Server can't perform arithmetic on text values, hence the failure.

How to Fix It #

Step 1: Convert Text Fields to Numeric Types Before Summing #

You need to explicitly convert those nvarchar fields to a numeric type first. Use TRY_CAST or TRY_CONVERT (safer than plain CAST because they return NULL if conversion fails instead of crashing the entire query), then wrap that in ISNULL to replace NULL values with 0 as you originally intended.

Here's the corrected version of your query snippet:

SUM(ISNULL(TRY_CAST(T1.RecvQty AS DECIMAL(18,2)), 0)) AS '3rd Party Received Qty',
SUM(ISNULL(TRY_CAST(T0.Quantity AS DECIMAL(18,2)) - TRY_CAST(T0.Quantout AS DECIMAL(18,2)), 0)) AS 'SAP Onhand',
SUM(
    ISNULL(TRY_CAST(T1.recvQty AS DECIMAL(18,2)), 0) - 
    ISNULL(TRY_CAST(T0.Quantity AS DECIMAL(18,2)) - TRY_CAST(T0.QuantOut AS DECIMAL(18,2)), 0)
) AS 'Varience'

(I used DECIMAL(18,2) assuming your quantities might have decimals. Adjust the precision/scale to match your data — use INT if all values are whole numbers.)

Step 2: Fix the Root Cause (Recommended) #

If these fields are meant to store numeric quantities (which they clearly are, since you're summing them), the best long-term fix is to update your table schema. Changing the columns from nvarchar to a numeric type will prevent this error from recurring and make your queries more efficient.

For example, to alter T1.RecvQty to DECIMAL(18,2):

ALTER TABLE T1
ALTER COLUMN RecvQty DECIMAL(18,2) NULL;

Note: Back up your data first, and clean up any non-numeric values in the column before making this change — otherwise the alter statement will fail.

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