SQL查询中的算术溢出转换表达式(替换和大小写语句)时出错

Question

我在运行以下查询时收到上述错误，我已经尝试了几个选项，例如添加 cast(见下文(，但我仍然收到错误。

我在下面的查询中只包含了与错误相关的字段。如果我chiffre_daffaire_Caisse发表评论，则查询有效。感谢您的建议

With CTE as (
SELECT 
WHEN D.F1034=3 THEN  REPLACE(CONVERT(VARCHAR(10), 
CONVERT(DECIMAL(10,3),D.F64), 111), '.', '')
                           ELSE 0 
END as Qty_Sold,

CASE WHEN [POS_TAB].F81 = 1 THEN REPLACE(CONVERT(VARCHAR(10), 
CONVERT(DECIMAL(10,2),  Cast(CONVERT(DECIMAL(10,2), 
Cast(CONVERT(DECIMAL(10,2),D.F65/NULLIF(D.F64,0)) as nvarchar) * cast(1.15 
as numeric(10,2)) ) as nvarchar) ), 111), '.', '')  ELSE
REPLACE(CONVERT(VARCHAR(10), CONVERT(DECIMAL(10,2), 
Cast(CONVERT(DECIMAL(10,2),D.F65/NULLIF(D.F64,0)) as nvarchar) ), 111), '.', 
'')
END as UnitPrice

FROM 
             [dbo].[RPT_ITM_D] D
             LEFT OUTER JOIN [dbo].[RPT_ITM_D] REF ON (D.F01=REF.F01 AND 
D.F254=REF.F254 AND D.F126=REF.F126 AND D.F1056=REF.F1056 AND 
D.F1057=REF.F1057 AND REF.F1034 IN (3011,3012))
             LEFT OUTER JOIN [dbo].[RPT_ITM_D] QTY ON (D.F01=QTY.F01 AND 
 D.F254=QTY.F254 AND D.F126=QTY.F126 AND D.F1056=QTY.F1056 AND 
D.F1057=QTY.F1057 AND QTY.F1034 IN (3011,3012))
             LEFT OUTER JOIN [dbo].[POS_TAB] ON (D.F01=POS_TAB.F01)
             LEFT OUTER JOIN [dbo].SDP_TAB ON (POS_TAB.F04=SDP_TAB.F04)
             LEFT OUTER JOIN [dbo]. DEPT_TAB ON (SDP_TAB.F03=DEPT_TAB.F03)
            -- LEFT OUTER JOIN [dbo]. PRICE_TAB ON  D.F01 = PRICE_TAB.F01
     where 
             D.F1034 IN (3,3012)
                          AND
               D.F254  = '2017/11/18' 

)
 Select 
 --SUM((Qty_Sold * UnitPrice)/100)  AS chiffre_daffaire_Caisse,  (Give Error 
 Arithmetic overflow error converting expression to data type int.
 Warning: Null value is eliminated by an aggregate or other SET operation.)
--SUM(CAST(((Qty_Sold * UnitPrice)/100) AS  numeric(12, 0))), (Give Error 
Arithmetic overflow error converting expression to data type int.
Warning: Null value is eliminated by an aggregate or other SET operation.)
SUM(isnull(cast(((Qty_Sold * UnitPrice)/100) as numeric(12, 0)),0)) , (Give 
Error : Arithmetic overflow error converting expression to data type int.)
-- I have even tried BigInt but its not working
from CTE

Answer 1

您可以使用 TRY_CONVERT/CAST 查找有问题的值，如下所示

TRY_CONVERT(DECIMAL(10,2),1)

上面的查询不会失败，但对于不转换的值会有空值

Answer 2

我已经找到了解决方案。

我必须cast Qty_Sold Numeric或BigInt如下所示。

SUM(isnull(cast(((CAST(Qty_Sold AS numeric) * UnitPrice)/100) as numeric(12, 0)),0))