Solved

Arithmetic overflow error converting numeric to data type numeric.

Posted on 2009-06-29
1
1,478 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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

914 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

20 Experts available now in Live!

Get 1:1 Help Now