Recently, I've created a view which is union of the customer and vendor transaction. I've added a computed column to calculated remain AmountMST. Everything works fine until I faced "Arithmetic overflow error converting numeric to data type numeric" error on other server. As I investigated on the data I discovered that there are some transaction for the vendor which has large amount for example: 10596974868300600.
I check the view in sql and see that the computed column is cast to numeric(32,16). This is the view in sql server
SELECT T1.CUSTACCOUNT AS CUSTACCOUNT, T1.VENDACCOUNT AS VENDACCOUNT, T1.VOUCHER AS VOUCHER, T1.TRANSTYPE AS TRANSTYPE, T1.TRANSDATE AS TRANSDATE,
T1.CURRENCYCODE AS CURRENCYCODE, T1.POSTINGPROFILE AS POSTINGPROFILE, T1.DOCUMENTDATE AS DOCUMENTDATE, T1.DUEDATE AS DUEDATE, T1.INVOICE AS INVOICE, T1.TXT AS TXT,
T1.AMOUNTMST AS AMOUNTMST, T1.EXCHADJUSTMENT AS EXCHADJUSTMENT, T1.SETTLEAMOUNTMST AS SETTLEAMOUNTMST, T1.LASTSETTLEDATE AS LASTSETTLEDATE,
T1.LASTSETTLEVOUCHER AS LASTSETTLEVOUCHER, T1.CLOSED AS CLOSED, T1.PARTYRECID AS PARTYRECID, T1.MAINACCOUNTID AS MAINACCOUNTID, T1.MAINACCOUNTNAME AS MAINACCOUNTNAME,
T1.POSTINGPROFILENAME AS POSTINGPROFILENAME, T1.DATAAREAID AS DATAAREAID, T1.PARTITION AS PARTITION, 1010 AS RECID, (CAST(('CustTrans') AS NVARCHAR(10))) AS TABLENAME,
(CAST((((T1.AMOUNTMST) (T1.EXCHADJUSTMENT)) - (T1.SETTLEAMOUNTMST)) AS NUMERIC(32, 16))) AS REMAINAMOUNT, (CAST((CASE WHEN T1.CLOSED = N' ' THEN 1 ELSE 2 END) AS INT))
AS TRANSSTATUS, (CAST((0) AS INT)) AS ACCOUNTTYPE
FROM CUSTWITHRELATEDVEND T1
UNION
SELECT T1.CUSTACCOUNT AS CUSTACCOUNT, T1.VENDACCOUNT AS VENDACCOUNT, T1.VOUCHER AS VOUCHER, T1.TRANSTYPE AS TRANSTYPE, T1.TRANSDATE AS TRANSDATE,
T1.CURRENCYCODE AS CURRENCYCODE, T1.POSTINGPROFILE AS POSTINGPROFILE, T1.DOCUMENTDATE AS DOCUMENTDATE, T1.DUEDATE AS DUEDATE, T1.INVOICE AS INVOICE, T1.TXT AS TXT,
T1.AMOUNTMST AS AMOUNTMST, T1.EXCHADJUSTMENT AS EXCHADJUSTMENT, T1.SETTLEAMOUNTMST AS SETTLEAMOUNTMST, T1.LASTSETTLEDATE AS LASTSETTLEDATE,
T1.LASTSETTLEVOUCHER AS LASTSETTLEVOUCHER, T1.CLOSED AS CLOSED, T1.PARTYRECID AS PARTYRECID, T1.MAINACCOUNTID AS MAINACCOUNTID, T1.MAINACCOUNTNAME AS MAINACCOUNTNAME,
T1.POSTINGPROFILENAME AS POSTINGPROFILENAME, T1.DATAAREAID AS DATAAREAID, T1.PARTITION AS PARTITION, 1010 AS RECID, (CAST(('VendTrans') AS NVARCHAR(10))) AS TABLENAME,
(CAST((((T1.AMOUNTMST) (T1.EXCHADJUSTMENT)) - (T1.SETTLEAMOUNTMST)) AS NUMERIC(32, 16))) AS REMAINAMOUNT, (CAST((CASE WHEN T1.CLOSED = N' ' THEN 1 ELSE 2 END) AS INT))
AS TRANSSTATUS, (CAST((1) AS INT)) AS ACCOUNTTYPE
FROM VENDWITHRELATEDCUST T1
when I change numeric(32,16) to (32,6) and run select query on the view no error comes. but when the data type is numeric(32,16) it failed. Also using 33 instead of 32 will be fix the issue.
But The problem is even I cast the calculation to numeric(32,6) (using sysComputed::Cast) at the end casting to (32,16) is added automatically. it seems every real computed column is stored as numeric(32,16).
This is my computed method:
public static server str remainAmountMST(int _branchNum)
{
#define.CompView(CustVendView)
#define.CompCustDS(CustWithRelatedVend)
#define.CompVendDS(VendWithRelatedCust)
#define.AmountMstCol(AmountMST)
#define.ExchAdjustCol(ExchAdjustment)
#define.SettlementCol(SettleAmountMST)
if(_branchNum)
{
return SysComputedColumn::subtract(
SysComputedColumn::add(
SysComputedColumn::returnField(
tableStr(#CompView),
identifierStr(#CompCustDS),
fieldStr(#CompCustDS, #AmountMstCol)),
SysComputedColumn::returnField(
tableStr(#CompView),
identifierStr(#CompCustDS),
fieldStr(#CompCustDS, #ExchAdjustCol))
),
SysComputedColumn::returnField(
tableStr(#CompView),
identifierStr(#CompCustDS),
fieldStr(#CompCustDS, #SettlementCol))
);
}
else
{
return SysComputedColumn::subtract(
SysComputedColumn::add(
SysComputedColumn::returnField(
tableStr(#CompView),
identifierStr(#CompVendDS),
fieldStr(#CompCustDS, #AmountMstCol)),
SysComputedColumn::returnField(
tableStr(#CompView),
identifierStr(#CompVendDS),
fieldStr(#CompCustDS, #ExchAdjustCol))
),
SysComputedColumn::returnField(
tableStr(#CompView),
identifierStr(#CompVendDS),
fieldStr(#CompCustDS, #SettlementCol))
);
}
So How can I cast the computed column to numeric(32,6) ?
