?
Solved

Arithmetic overflow error converting numeric to data type numeric.

Posted on 2009-06-29
1
Medium Priority
?
1,693 Views
Last Modified: 2012-06-27
Please note, the SQL is handled dynamically by the SQL Server, therefore some items in my WHERE clause will look odd to you. Please disregard. I am getting the following Error in my report.

Server: Msg 8115, Level 16, State 8, Line 112
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.
/* Billing Status Report 11/22/03 CySolutions 
Revised 5/6/06 */
SET NOCOUNT ON
 
CREATE TABLE #Records
       (
         TicketNumber VARCHAR(20) ,
         Visit DATETIME ,
         LastDateFiled DATETIME ,
         FirstDateFiled DATETIME ,
         InsBalance MONEY ,
         PatBalance MONEY ,
         PrimaryInsuranceCarrier VARCHAR(50) ,
         CurrentInsuranceCarrier VARCHAR(50) ,
         CurrentCarrier NUMERIC(1 , 0) ,
         BillStatus VARCHAR(50) ,
         BillStatusID INT ,
         Entered DATETIME ,
         FilingType INT ,
         DaysSinceFiled INT ,
         PatientName VARCHAR(50) ,
         VisitOwner VARCHAR(50) ,
         Description VARCHAR(100) ,
         Doctor VARCHAR(100) ,
         Credentialed VARCHAR(1)
       )
 
-- Insert the New Records
IF '1' = '1'
   OR 1 IN ( NULL ) 
   INSERT INTO
    #Records
    SELECT
        ISNULL(a.TicketNumber , 'None') ,
        CONVERT(VARCHAR , a.ApptStart , 101) ,
        '' ,
        '' ,
        0 ,
        0 ,
        ISNULL(ic.ListName , 'None') ,
        ISNULL(ic.ListName , 'None') ,
        1 ,
        'New' ,
        1 ,
        '' ,
        0 ,
        0 ,
        pp.Last + ', ' + pp.First + ' - ' + pp.PatientID ,
        'Appointment' ,
        '' ,
        d.ListName ,
        CASE WHEN icd.InsuranceCarriersDoctorID IS NOT NULL THEN 'X'
             ELSE ' '
        END
    FROM
        dbo.InsuranceCarriers ic
        INNER JOIN dbo.PatientInsurance pi ON ic.InsuranceCarriersId = pi.InsuranceCarriersId
        RIGHT OUTER JOIN dbo.Appointments a ON pi.PatientProfileId = a.OwnerId
        INNER JOIN dbo.PatientProfile pp ON a.OwnerID = pp.PatientProfileID
        INNER JOIN DoctorFacility d ON ISNULL(a.DoctorID , a.ResourceID) = d.DoctorFacilityID
        LEFT JOIN InsuranceCarriersDoctor icd ON pi.InsuranceCarriersID = icd.InsuranceCarriersID
                                                 AND ISNULL(a.DoctorID , a.ResourceID) = icd.DoctorID
    WHERE
        ( pi.OrderForClaims = 1 )
        AND ( a.PatientVisitId IS NULL )
        AND ( a.ApptKind = 1 )
        AND (
              a.HideNewVisit IS NULL
              OR a.HideNewVisit = 0
            )
        AND (
              a.Canceled IS NULL
              OR a.Canceled = 0
            )
        AND ( a.ApptStart < GETDATE() )
        AND a.ApptStart >= ISNULL(NULL , '1/1/1900')
        AND a.ApptStart < DATEADD(day , 1 , ISNULL(NULL , '1/1/3000'))
        AND  --Filter on insurance carrier
        (
          (
            NULL IS NOT NULL
            AND ic.InsuranceCarriersId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on facility
        (
          (
            NULL IS NOT NULL
            AND a.FacilityID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on insurance group
        (
          (
            NULL IS NOT NULL
            AND ic.InsuranceGroupId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Doctor
        (
          (
            NULL IS NOT NULL
            AND a.ResourceID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
                
-- Enter the Visit Records
INSERT INTO
    #Records
    SELECT
        pv.TicketNumber ,
        pv.Visit ,
        pv.LastFiledDate ,
        pv.FirstFiledDate ,
        pva.InsBalance ,
        pva.PatBalance ,
        ISNULL(ic.ListName , 'None') ,
        ISNULL(ic.ListName , 'None') ,
        pv.CurrentCarrier ,
        bs.Description ,
        pv.BillStatus ,
        pv.Entered ,
        ISNULL(pv.FilingType , 0) ,
        CASE WHEN pv.LastFiledDate IS NULL THEN 0
             ELSE DATEDIFF(d , pv.LastFiledDate , GETDATE())
        END ,
        pp.Last + ', ' + pp.First + ' - ' + pp.PatientID ,
        ISNULL(vo.Description , 'No Owner') ,
        ISNULL(LEFT(pv.Description , 100) , '') ,
        d.ListName ,
        CASE WHEN icd.InsuranceCarriersDoctorID IS NOT NULL THEN 'X'
             ELSE ' '
        END
    FROM
        dbo.PatientVisit pv
        INNER JOIN dbo.PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
        LEFT OUTER JOIN dbo.InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
        LEFT OUTER JOIN dbo.PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
        LEFT OUTER JOIN dbo.InsuranceCarriers cic ON pv.CurrentInsuranceCarriersId = cic.InsuranceCarriersId
        LEFT OUTER JOIN dbo.MedLists bs ON bs.JoinId = pv.BillStatus
                                           AND bs.TableName = 'BillStatus'
        INNER JOIN DoctorFacility d ON pv.DoctorID = d.DoctorFacilityID
        LEFT OUTER JOIN MedLists vo ON pv.VisitOwnerMID = vo.MedListsID
        LEFT JOIN InsuranceCarriersDoctor icd ON pv.CurrentInsuranceCarriersId = icd.InsuranceCarriersID
                                                 AND pv.DoctorID = icd.DoctorID
    WHERE
        pv.Visit >= ISNULL(NULL , '1/1/1900')
        AND pv.Visit < DATEADD(day , 1 , ISNULL(NULL , '1/1/3000'))
        AND pv.BillStatus NOT IN ( 12 , 10 )
	--Filter on BillStatus
        AND (
              (
                NULL IS NULL
                AND '1' = '1'
              )
              OR (
                   '1' = '2'
                   AND NULL IS NULL
                 )
              OR pv.BillStatus IN ( NULL )
            )
        AND  --Filter on insurance carrier
        (
          (
            NULL IS NOT NULL
            AND ic.InsuranceCarriersId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on facility
        (
          (
            NULL IS NOT NULL
            AND pv.FacilityID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on insurance group
        (
          (
            NULL IS NOT NULL
            AND ic.InsuranceGroupId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND -- Carrier priority to include
        (
          ( 1 = 1 )
          OR (
               1 = 2
               AND pv.CurrentCarrier = 1
             )
          OR (
               1 = 3
               AND pv.CurrentCarrier > 1
             )
        )
        AND  --Filter on visitowner
        (
          (
            NULL IS NOT NULL
            AND pv.VisitOwnerMID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Doctor
        (
          (
            NULL IS NOT NULL
            AND pv.DoctorID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
 
IF 0 <> 1 
   SELECT
    *
   FROM
    #Records
ELSE 
   SELECT
    *
   FROM
    #Records
   WHERE
    BillStatus <> 'New'

Open in new window

0
Comment
Question by:Jeff S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 41

Accepted Solution

by:
ralmada earned 2000 total points
ID: 24736991
the error typically occurs when you are trying to pass a numeric value that is too large...
The only numeric column I see is in your example is CurrentCarrier NUMERIC(1 , 0) ,

I'm not sure why you need a numeric without decimals. I would just use an integer or add more digits to the current one. NUMERIC(2,1)
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

801 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question