Conversion failed when converting the varchar valu

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 2000 Forums

Transact-SQL (2000)

Conversion failed when converting the varchar valu

Author

Topic

JeffS23
Posting Yak Master

212 Posts

Posted - 2009-09-29 : 12:04:17

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' and HDID IN (53,54)

I got back several 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'

--select * from obs where ObsValue = '6.2' and HDID IN (53,54)

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]

Ifor
Aged Yak Warrior

700 Posts

Posted - 2009-09-29 : 12:53:00

The quick way to solve your problem is to compare to a float, not an int.

eg:


HDID = 54 AND
CASE WHEN ISNUMERIC(REPLACE(obs.OBSVALUE , ' mmHg' , '')) = 1 THEN REPLACE(obs.OBSVALUE , ' mmHg' , '')
     ELSE NULL
END < 140.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-29 : 13:54:04

cross post

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=133495

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources