Jeff S
asked on
SQL 2005 - Conversion failed when converting the varchar value '6.2' to data type int.
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.
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
Use decimal instead. Ints are whole numbers
ASKER
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.OBSV ALUE , ' mmHg' , '')) = 1 then replace(obs.OBSVALUE , ' mmHg' , '')
else NULL
end < 140
) OR
(
HDID = 53 and
case when isnumeric(replace(obs.OBSV ALUE , ' 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.OBSV ALUE , ' mmHg' , '')) = 1 then replace(obs.OBSVALUE , ' mmHg' , '')
else NULL
end < 140
) OR
(
HDID = 53 and
case when isnumeric(replace(obs.OBSV ALUE , ' mmHg' , '')) = 1 then replace(obs.OBSVALUE , ' mmHg' , '')
else NULL
end < 90
)
)
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.OBSV
else NULL
end < 140
) OR
(
HDID = 53 and
case when isnumeric(replace(obs.OBSV
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.OBSV
else NULL
end < 140
) OR
(
HDID = 53 and
case when isnumeric(replace(obs.OBSV
else NULL
end < 90
)
)
ASKER
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."
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."
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
ASKER
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.