troubleshooting Question

SQL Server 2000 Database Error: Arithmetic overflow error converting numeric to data type numeric

Avatar of ldcray
ldcray asked on
Microsoft SQL Server
18 Comments1 Solution563 ViewsLast Modified:
Not sure what happened to this part of the sql ... yesterday it was working fine.  I'm having a hard time debugging on this part of the sql.
Insert dbo.tPolicyWhse(RecordType, Program, Policy_Number, Branch, status, TIV, ExcludeTRIA, Billing_type,
cancel_date, Issue_date, eff_date, exp_date, policy_type, Commission, origination, ProcDate, WeekNo, ProcYr, Procmon, Invoice_No,  Business_class,
PolicySLTaxs, PolicyFees,  policy_QuoteKy, policy_RenewalQuoteKy,policy_Quote_number, policy_app_number, renewal_number, 
policy_databaseIdentifier, UserId, LOBName, DOCIssDate, DOCEffDate, DOCExpDate, DOCProcDate, org_prem_amt, prem_amt, NumExtensions, TotalMonthsExtended, WARChange,
Interface, LiabilityPrem, LiabilityTIV, grp_SAS,policy_type_Desc)


--***********************************************************************************************************************************
SELECT distinct 0 AS RecordType, Program, Policy_Number, Branch, status, COALESCE(TIV,0) AS TIV, ExcludeTRIA, Billing_type,
dbo.getDatePart(cancel_date) AS cancel_date, dbo.getDatePart(Issue_date) AS Issue_date, dbo.getDatePart(policy_eft) AS eff_date,
dbo.getDatePart(policy_exp) AS exp_date, COALESCE(policy_type,0) as policy_type, COALESCE(Commission,0) as Commission, COALESCE(origination,0) AS origination,
CASE WHEN CONVERT(DATETIME,Issue_date,102) > CONVERT(DATETIME,policy_eft,102)THEN dbo.getDatePart(Issue_date)
    Else
     dbo.getDatePart (policy_eft)
END AS ProcDate,
DATEPART(ww,CASE WHEN CONVERT(DATETIME,Issue_date,102) > CONVERT(DATETIME,policy_eft,102)THEN dbo.getDatePart(Issue_date)
    Else
     dbo.getDatePart (policy_eft)
END) as WeekNo, 
CASE WHEN CONVERT(DATETIME,Issue_date,102) > CONVERT(DATETIME,policy_eft,102) THEN Year(Issue_date) ELSE Year(policy_eft)
END AS ProcYr,
CASE WHEN CONVERT(DATETIME,Issue_date,102) > CONVERT(DATETIME,policy_eft,102) THEN Month(Issue_date)
    Else
     Month (policy_eft)
END AS Procmon,
Invoice_No, Business_class,
COALESCE(MAR_SL_FEE,0)+COALESCE(MAR_SL_TAX1,0)+COALESCE(MAR_SL_TAX2,0) as PolicySLTaxs, 
case program
     WHEN 115 THEN 0
    else
     COALESCE(MAR_POL_FEE,0)+COALESCE(MAR_INS_FEE,0)+COALESCE(MAR_CER_FEE,0)
end as PolicyFees,
COALESCE(QuoteKy,0) as QuoteKy, COALESCE(RenewalQuoteKy,0) as RenewalQuoteKy,  COALESCE(Quote_number,0) as Quote_number, 
COALESCE(app_number,0) as app_number, COALESCE(renewal_number,0) as renewal_number,
databaseIdentifier, UserId, 
dbo.LOLName(Program) AS LOBName,
dbo.getDayofCentury(Issue_date) AS DOCIssDate,
dbo.getDayofCentury(policy_eft) AS DOCEffDate,
dbo.getDayofCentury(policy_exp) AS DOCExpDate,
dbo.getDAYofCentury(CASE WHEN CONVERT(DATETIME,Issue_date,102) > CONVERT(DATETIME,policy_eft,102)
      THEN dbo.getDatePart(Issue_date)
Else dbo.getDatePart (policy_eft)
END ) AS DOCProcDate,
COALESCE(org_prem_amt,0) AS org_prem_amt, COALESCE(prem_amt,0) AS prem_amt,
COALESCE(NumExtensions,0) AS NumExtensions, COALESCE(TotalMonthsExtended,0) AS TotalMonthsExtended, COALESCE(WarPrem,0) AS WARChange,
COALESCE(Interface,0) as Interface, COALESCE(LiabilityPrem,0) AS LiabilityPrem, COALESCE(LiabilityTIV,0) AS LiabilityTIV, dbo.grp_SAS(program) AS grp_SAS,
dbo.AircraftPolicyType(Program, Policy_type) as policy_type_Desc
FROM dbo.tmp_Policy
WHERE (NOT EXISTS(SELECT 1 FROM dbo.tPolicyWhse WHERE dbo.tmp_Policy.databaseIdentifier = dbo.tPolicyWhse.policy_databaseIdentifier)
 AND (dbo.tmp_Policy.Issue_date >= CONVERT(DATETIME, '2005-11-01 00:00:00',102)) 
-- AND (dbo.tmp_Policy.Issue_date IS NOT null))
 AND (dbo.tmp_Policy.MarkedAsDeleted = 0))


zone Laptops removed by a3
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 18 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 18 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros