Solved

Arithmetic overflow error converting numeric to data type numeric.

Posted on 2009-06-29
1
1,464 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
1 Comment
 
LVL 41

Accepted Solution

by:
ralmada earned 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now