Link to home
Start Free TrialLog in
Avatar of Jeff S
Jeff SFlag for United States of America

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

Avatar of chapmandew
chapmandew
Flag of United States of America image

Use decimal instead. Ints are whole numbers
Avatar of Jeff S

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

Avatar of Jeff S

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

Avatar of Jeff S

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.

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

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial