Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2009-09-26
6
Medium Priority
?
1,473 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
Comment
Question by:Jeff S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 60

Expert Comment

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

Author Comment

by:Jeff S
ID: 25430843
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:Jeff S
ID: 25430999
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 25431354
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:Jeff S
ID: 25431892
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 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
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

598 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