Solved

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

Posted on 2009-09-26
1,351 Views
Last Modified: 2012-05-07
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

Open in new window

0
Question by:JeffSturgeon2002
    6 Comments
     
    LVL 60

    Expert Comment

    by:chapmandew
    Use decimal instead. Ints are whole numbers
    0
     
    LVL 7

    Author Comment

    by:JeffSturgeon2002
    chapmandew -
    When I comment this section out, my query returns results. I think its something in this section where I am off, however I am not 100%.
    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
          )
        )

    0
     
    LVL 7

    Author Comment

    by:JeffSturgeon2002
    Looks like its coming from the obs.OBSVALUE field.
    When I used this query:
    SELECT * FROM obs where OBSVALUE = '6.2'
    I got back 36 rows.
    So I think this is whats causing my issue, since my original error message was "Conversion failed when converting the varchar value '6.2' to data type int."
    0
     
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    what if you do explicit casts?
    cast( case when isnumeric(replace(obs.OBSVALUE , ' mmHg' , '')) = 1 then replace(obs.OBSVALUE , ' mmHg' , '')
                 else NULL
            end as decimal(20,4)) < 90.0
    

    Open in new window

    0
     
    LVL 7

    Author Comment

    by:JeffSturgeon2002
    angelIII
    I tried the below mentioned coding and got back this message.

    Msg 8115, Level 16, State 6, Line 155
    Arithmetic overflow error converting varchar to data type numeric.
    Msg 8114, Level 16, State 5, Line 155
    Error converting data type varchar to numeric.
    The statement has been terminated.

    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
    
                cast(case when isnumeric(replace(obs.OBSVALUE , ' mmHg' , '')) = 1 then replace(obs.OBSVALUE , ' mmHg' , '')
    
                     else NULL
    
                end as decimal(20,4)) < 140
    
              ) OR
    
              (
    
                HDID = 53 and
    
               cast(case when isnumeric(replace(obs.OBSVALUE , ' mmHg' , '')) = 1 then replace(obs.OBSVALUE , ' mmHg' , '')
    
                     else NULL
    
                end as decimal(20,4)) < 90.0
    
              )
    
            ) and
    
            obsdate >= @StartDate AND
    
            obsdate <= @EndDate
    
          group by
    
            pid
    
        ) t on obs.PID = t.PID and
    
               obs.obsdate = t.obsmax
    
    WHERE
    
        (
    
          (
    
            HDID = 54 AND
    
            cast(case when isnumeric(replace(obs.OBSVALUE , ' mmHg' , '')) = 1 then replace(obs.OBSVALUE , ' mmHg' , '')
    
                 else NULL
    
            end as decimal (20,4)) < 140
    
          ) OR
    
          (
    
            HDID = 53 and
    
            cast(case when isnumeric(replace(obs.OBSVALUE , ' mmHg' , '')) = 1 then replace(obs.OBSVALUE , ' mmHg' , '')
    
                 else NULL
    
            end as decimal (20,4)) < 90
    
          )
    
        )
    
     
    
     
    
    Select
    
        *
    
    FROM
    
        #TMP 
    
    Drop Table #tmp
    
    Drop Table #Patients
    

    Open in new window

    0
     
    LVL 142

    Accepted Solution

    by:
    >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
    0

    Write Comment

    Please enter a first name

    Please enter a last name

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

    Featured Post

    Learn The Basics of Ethical Hacking & Pen Testing

    Computer and network security is one of the fastest growing and most essential industries in technology, meaning companies will pay big bucks for ethical hackers. This is the perfect course to leap into this lucrative career, learning how to use ethical hacking to reveal ...

    Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    875 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

    11 Experts available now in Live!

    Get 1:1 Help Now