Arithmetic overflow error converting int to data type numeric. SQL 2005

Jeff S
Jeff S used Ask the Experts™
on
Please note the SQL is handled dynamically by the SQL Server, therefore some items in my WHERE clauses will look odd to you PLEASE ignore this as its not an issue. I am getting the following error in my report and need some help.

Msg 8115, Level 16, State 8, Line 100
Arithmetic overflow error converting int to data type numeric.
The statement has been terminated.


/* Billing Status Report 11/22/03  
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
Does it work with

CurrentCarrier int,

instead of

CurrentCarrier numeric(1, 0),

in your tmp table declaration?
Analysing your SQL, the only field that causes the overflow is CurrentCarrier.

Are you sure pv.CurrentCarrier returns a number with only 1 digit on the second INSERT clause?

Author

Commented:
it should only return one digit unless someones gone in and added in something weird.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
I ran this code:

select distinct pv.CurrentCarrier from patientvisit pv
and got back:
 NULL
0
1
2
3
4
5
7
8
11
14
As I've suspected, you have two values that overflows the size of CurrentCarrier field on temporary table.

To solve this problem, you should change the datatype of this field on CREATE TABLE clause to the same of pv.CurrentCarrier.

Example:

CREATE TABLE #Records
    (
     ...
      CurrentCarrier numeric(2, 0), -- or  CurrentCarrier int,
     ...
     )

Author

Commented:
THANKS!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial