SQL 2005 - Conversion failed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2005 - Conversion failed

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-09-26 : 15:32:54

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 157
Conversion failed when converting the varchar value '6.2' to data type int.

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."

[CODE]
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
[/CODE]

asgast
Posting Yak Master

149 Posts

Posted - 2009-09-28 : 03:44:46

ADD isnumeric(replace(obs.OBSVALUE , ' mmHg' , '')) = 1 to your were parts

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 00:37:13

isnumeric() is not fully reliable. it will also give one for formats like 1e2,... which is not an int value. better to use round after converting to numeric or decimal types.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources