SQL 2005 - Conversion failed when converting the varchar value '6.2' to data type int.

    Question by:
    On

    Topics:

    Please note, the SQL is handled dynamically by the server, therefore some items in my WHERE clause will look odd to you - please disregard this. I am getting the following error and need a hand.

    Msg 245, Level 16, State 1, Line 113
    Conversion failed when converting the varchar value '6.2' to data type int.

    SET NOCOUNT ON
     
    CREATE TABLE #Patients (
    	PatientProfileID int,
    	PatientID varchar(15),
    	MRN varchar (15),
    	PID numeric,
    	PatientName varchar(100),
    	Birthdate datetime,
    	Age varchar (15),
    	Sex varchar(1),
    	RaceCode varchar(50),
    	EthnicityCode varchar(50),
    	RaceMID2 int
    )
     
    DECLARE @AgeDate datetime
    DECLARE @DXDate datetime
     
    SET @AgeDate = '12/31/' + CONVERT(varchar, YEAR('08/31/2009'))
    SET @DXDate = '06/30/' + CONVERT(varchar, YEAR('08/31/2009'))
     
    INSERT INTO #Patients
    SELECT pp.PatientProfileID, 
    	pp.PatientID,
    	pp.MedicalRecordNumber AS MRN,
    	pp.PID,
    	RTRIM(RTRIM(ISNULL(pp.Last, '') + ' ' + ISNULL(pp.Suffix, '')) + ', ' + ISNULL(pp.First, '') + ' ' + ISNULL(pp.Middle, '')) AS PatientName,  
    	pp.Birthdate,
    	(DATEDIFF(day,pp.Birthdate,@AgeDate) /365.25) as Age, 
    	pp.Sex,
    	CASE WHEN r.Code = 'H' OR e.Code = 'H' THEN 'Hispanic/Latino' 
    		WHEN r.Code = 'A' THEN 'Asian'
    		WHEN r.Code = 'NH' THEN 'Native Hawaiian'
    		WHEN r.Code = 'OPI' THEN 'Other Pacific Islander'
    		WHEN r.Code = 'B' THEN 'Black/African American'
    		WHEN r.Code = 'AI' THEN 'American Indian/Alaskan Native'
    		WHEN r.Code = 'W' THEN 'White'
    		WHEN r.Code = 'M' THEN 'More than one race'
    		WHEN r.Code = 'U' THEN 'Unreported'
    		ELSE 'Unreported' END AS RaceCode,
    	CASE WHEN r.Code = 'H'  OR e.Code = 'H' THEN 'Hispanic/Latino' 
    		ELSE 'All Others' END AS EthnicityCode, 
    	cri.RaceMID2
     
     
    FROM PatientProfile pp
    	LEFT JOIN PatientVisit pv ON pp.PatientProfileID = pv.PatientProfileId
    	LEFT JOIN cusCRIInterview cri on pp.patientprofileid = cri.patientprofileid
    	LEFT JOIN Medlists r on pp.RaceMID = r.MedListsID
    	LEFT JOIN cusCRIMedLists e on cri.RaceMID2 = e.MedListsID
    	
    WHERE 
           --Filter on facility
    	(
    	(NULL IS NOT NULL AND pv.FacilityID IN (NULL)) OR
    	(NULL IS NULL)
    	)
         	 AND 
    	--Filter on Company
    	(
    	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
    	(NULL IS NULL)
    	)
    	AND
    	(DATEDIFF(day,pp.Birthdate,@AgeDate) /365.25) >17.99
    	AND 
    	DATEPART(Year,pv.visit) = DATEPART(Year,'08/31/2009') -- limit to visits in this year
    	AND
    	pp.PatientProfileID in -- now we filter on diagnosis, independently
    	(
    		SELECT PatientProfileID
    		FROM PatientProfile pp
    		JOIN Orders o ON pp.PID = o.PID
    		JOIN Orddx  ON o.dxgroupid = orddx.dxgroupid
    		WHERE
    		orddx.dxcode  LIKE ('icd-401.%') 
    		AND  o.XID=1000000000000000000
    		AND o.OrderDate <= @DXDate
    	)
    GROUP BY pp.PatientProfileID,pp.PatientId, pp.MedicalRecordNumber, pp.PID, pp.Birthdate, pp.Sex, pp.Last, pp.Suffix, pp.First, pp.Middle, r.code, e.code, cri.raceMID2
    HAVING COUNT(*)>1 -- must have 2+ visits this year
     
     
    SELECT PatientProfileID,
    	count(*) as cnt,
    	PatientID,
    	MRN,
    	PID,
    	PatientName,  
    	Birthdate,
    	Age, 
    	Sex,
    	RaceCode,
    	EthnicityCode, 
    	RaceMID2
    INTO #tmp
    FROM #Patients 
    GROUP BY PatientProfileID, PatientID, MRN, PID, PatientName, Birthdate, Sex, age, RaceCode, EthnicityCode, RaceMID2
    ORDER BY	CASE
    	WHEN '1'  = 1 THEN MRN
    	WHEN '1'  = 2 THEN PatientName 
    	WHEN '1'  = 3 THEN PatientID ELSE NULL
    	END
     
    DECLARE @StartDate Datetime
    DECLARE @EndDate Datetime
     
    SET @StartDate  = CONVERT(varchar, YEAR('08/31/2009')) + '/01/01'
    SET @EndDate = CONVERT(varchar, YEAR('08/31/2009')) + '/12/31'
     
    ALTER TABLE #TMP ADD ObsValue varchar (2000) null
    Update #TMP
    Set ObsValue = '1'
    FROM OBS obs
    JOIN #TMP ON obs.PID = #TMP.pid
    join (select pid, max(obsdate) as obsmax from obs WHERE ((HDID = 54 AND case when isnumeric(replace(obs.OBSVALUE,' mmHg',''))=1 then replace(obs.OBSVALUE,' mmHg','') else NULL end < 140) OR (HDID = 53 and case when isnumeric(replace(obs.OBSVALUE,' mmHg',''))=1 then replace(obs.OBSVALUE,' mmHg','') else NULL end < 90)) 
    and obsdate >= @StartDate AND obsdate <= @EndDate group by pid) t on obs.PID=t.PID and obs.obsdate=t.obsmax
    WHERE ((HDID = 54 AND case when isnumeric(replace(obs.OBSVALUE,' mmHg',''))=1 then replace(obs.OBSVALUE,' mmHg','') else NULL end < 140) OR (HDID = 53 and case when isnumeric(replace(obs.OBSVALUE,' mmHg',''))=1 then replace(obs.OBSVALUE,' mmHg','') else NULL end < 90))
     
     
    Select * FROM #TMP 
    Drop Table #tmp
    Drop Table #Patients
    
                                    
    1:
    2:
    3:
    4:
    5:
    6:
    7:
    8:
    9:
    10:
    11:
    12:
    13:
    14:
    15:
    16:
    17:
    18:
    19:
    20:
    21:
    22:
    23:
    24:
    25:
    26:
    27:
    28:
    29:
    30:
    31:
    32:
    33:
    34:
    35:
    36:
    37:
    38:
    39:
    40:
    41:
    42:
    43:
    44:
    45:
    46:
    47:
    48:
    49:
    50:
    51:
    52:
    53:
    54:
    55:
    56:
    57:
    58:
    59:
    60:
    61:
    62:
    63:
    64:
    65:
    66:
    67:
    68:
    69:
    70:
    71:
    72:
    73:
    74:
    75:
    76:
    77:
    78:
    79:
    80:
    81:
    82:
    83:
    84:
    85:
    86:
    87:
    88:
    89:
    90:
    91:
    92:
    93:
    94:
    95:
    96:
    97:
    98:
    99:
    100:
    101:
    102:
    103:
    104:
    105:
    106:
    107:
    108:
    109:
    110:
    111:
    112:
    113:
    114:
    115:
    116:
    117:
    118:
    119:
    120:
    121:
    122:
    123:
    124:
    

    Select allOpen in new window

     

    Verified Answer?

    The member who asked this question verified this comment provided the solution that solved their problem.

    by:Posted on 2009-09-27 at 00:14:02ID: 25433025

    >Arithmetic overflow error converting varchar to data type numeric.

    that means you have numbers in that column that are bigger than the capacitiy of  decimal(20,4).
    ie:
    16 digits before the comma, 4 digits after the comma.
    please double-check your data, and increase as needed

    This content is available to Experts Exchange members

    See the answer now
    with your Free 30 Day Trial

    Get unlimited access to solutions & experts

    • 4,169,477 solved questions
    • 3,805 articles & videos
    • 15,413 tech experts

    Get Access Now

    Ask Your Tech Question. Get Expert Solutions.We will email you when an expert has commented on your question.

    We will never share this with anyone. Privacy Policy Terms of Use

    Select topics

    You may select up to five topics.

    Top Expert Contributor

    Essential articles and videos from the Experts

    More valuable questions with Expert answers

    201507-LO-Qu-065

    EXPERT WHO ANSWERED

    Guy Hengel [angelIII / …

    Guy Hengel [angelIII / a3] has answered 31,178 questions on Experts Exchange and is an expert in MS SQL Server 2005, MS SQL Server and Query Syntax.

    RELATED TOPICS view all topics

    1. MS SQL Server
      (152,759)
    2. MS SQL Server 2008
      (46,898)
    3. Query Syntax
      (45,486)
    4. Databases
      (52,093)
    5. .NET Programming
      (129,713)
    6. ASP.NET
      (121,160)
    7. SSRS
      (8,308)
    8. Windows 2003 Server
      (128,850)
    9. DB Reporting Tools
      (7,838)
    10. Visual Basic.NET
      (90,483)