相同的查询在相同 SQL 版本(包括 SP/KB 级别)的两台相同的服务器上运行,在一台服务器上出现相同的服务器和数据库级别设置错误(数据类型 smallint,值 = xxxxx 的算术溢出错误。)但在另一个。有任何想法吗?
谢谢,VJ
询问:
SELECT
c.cost_owner_key1
,pl_qty =
CASE
WHEN pa.PnlOwnerCode='C' AND pa.PnlOwnerSubCode = 'PR' THEN
COALESCE
(
CASE pa.BuySell
WHEN 'Buy' THEN -1
WHEN 'Sell' THEN 1
END, 1
) * ISNULL(c.cost_qty, 0)
ELSE
COALESCE
(
CASE pa.BuySell
WHEN 'Buy' THEN 1
WHEN 'Sell' THEN -1
END, 1
) * ISNULL(c.cost_qty, 0)
END
,pl_qty_factor =
CASE
WHEN c.cost_qty_uom_code = i.contr_qty_uom_code THEN 1
ELSE (a.secondary_actual_gross_qty / a.ai_est_actual_gross_qty)
END
,contr_qty_uom = c.cost_qty_uom_code
,alloc_num = c.cost_owner_key1
,alloc_item_num = c.cost_owner_key2
,CAST(c.cost_owner_key2 AS smallint)
FROM
pc_pnl_cp2_pnl_asof pa
INNER JOIN cost c WITH(NOLOCK) ON c.cost_num = pa.PnlRecordKey
AND pa.PnlOwnerCode = 'C'
INNER JOIN ai_est_actual a WITH(NOLOCK) ON a.alloc_num=c.cost_owner_key1
AND a.alloc_item_num=c.cost_owner_key2
AND a.ai_est_actual_num=c.cost_owner_key3
INNER JOIN trade_item i WITH(NOLOCK) ON i.trade_num = pa.TradeId
AND i.order_num = pa.OrderNumber
AND i.item_num = pa.ItemNumber
WHERE
pa.CostStatus != 'CLOSED'
AND pa.CostOwnerCode = 'AA'
AND pa.CostType = 'WPP'