Conversion failed when converting the varchar value 'See Comment' to data type int

Question

Conversion failed when converting the varchar value 'See Comment' to data type int

paytonm

Mr or Mrs. 500

Points: 529
More actions
February 17, 2009 at 7:08 pm

#126484

I am getting the error 'Conversion failed when converting the varchar value 'See Comment' to data type int' when I run a query, but only for one practice. I assume that it is some invalid entry, but can't find it. I have looked at the data in the lab_results_obx_.observ_value and did not see anything different than the other practices have. I did not write this query, but I believe that the piece of the query that is producing the error is the following:
--Creates a derived table named LDL that returns all LDL dates and values
(SELECT DISTINCT
CASE WHEN lab.practice_id IS NULL THEN flwst.practice_id ELSE lab.practice_id END AS practice_id, CASE WHEN lab.person_id IS NULL
THEN flwst.person_id ELSE lab.person_id END AS person_id, CASE WHEN lab.practice_id IS NULL THEN CONVERT(smalldatetime,
flwst.LDLDate) ELSE lab.lab_date END AS LDLDate, CASE WHEN lab.practice_id IS NULL
THEN flwst.cholldl ELSE lab.observ_value END AS LDLValue
FROM
-- Creates derived table named lab that pulls on LDL labs from the lab tables
(SELECT lab_nor.practice_id, lab_nor.person_id, CONVERT(char(08), patient_encounter.billable_timestamp, 112) AS lab_date,
lab_results_obx_.observ_value, lab_results_obx_.create_timestamp
FROM lab_results_obx AS lab_results_obx_ INNER JOIN
lab_results_obr_p ON lab_results_obx_.unique_obr_num = lab_results_obr_p.unique_obr_num INNER JOIN
lab_nor ON lab_results_obr_p.ngn_order_num = lab_nor.order_num INNER JOIN
patient_encounter ON lab_nor.enc_id = patient_encounter.enc_id
WHERE (lab_results_obx_.result_desc IN ('LDL', 'LDL CHOLESTEROL', 'LDL Cholesterol Calc', 'TOTAL LDL-CHOLESTEROL DIR')) AND (lab_results_obx_.observ_value IS NOT NULL AND
lab_results_obx_.observ_value NOT IN ('See Comments', 'Comment', 'TNP', 'Please refer to reflex test Comment', 'DUP')) AND (lab_results_obx_.observ_value <> '""') AND
(lab_results_obx_.observ_value <> '///') AND (lab_nor.practice_id = '0005')) AS lab FULL OUTER JOIN
-- Creates a derived table named flwsheet that pulls LDL lab values from the Diabetes flowsheet
(SELECT practice_id, person_id, lipidPanelDate AS LDLDate, lipidPanelStatus, cholLDL, create_timestamp
FROM Diabetes_Flwsheet_IPN_ AS Diabetes_Flwsheet_IPN__
WHERE (practice_id = '0005') AND (lipidPanelDate <> '') AND (cholLDL IS NOT NULL) AND
(lipidPanelDate IS NOT NULL)) AS flwst ON lab.practice_id = flwst.practice_id AND lab.person_id = flwst.person_id AND
lab.lab_date = flwst.ldldate) AS LDL) AS LDL1
WHERE Row = 1) AS LastLDL
ON status.practice_id = LastLDL.practice_id AND status.person_id = LastLDL.person_id
any help you can offer. thanks
michele

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

I would try sorting on that column desc to see what you find in there. Look either at the first or the last record to see if you have some funny data. Unfortunately the engine doesn't lie and something must be amiss in the conversion, i.e. data it can't handle. You will have to find that first.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

paytonm Mr or Mrs. 500 Points: 529 More actions · Answer 2

Thanks for your help. I found that there was another text phrase that I needed to explicitly exclude. I can see this happening again if a lab changes the wording when they send back a result. Is there a way to return only the numeric results and nothing else? This is the WHERE clause:

WHERE (lab_results_obx_.result_desc IN ('LDL', 'LDL CHOLESTEROL', 'LDL Cholesterol Calc', 'TOTAL LDL-CHOLESTEROL DIR')) AND (lab_results_obx_.observ_value IS NOT NULL AND

lab_results_obx_.observ_value NOT IN ('See Comments', 'Comment', 'TNP', 'Please refer to reflex test Comment', 'DUP')) AND (lab_results_obx_.observ_value <> '""') AND

(lab_results_obx_.observ_value <> '///') AND (lab_nor.practice_id = '0005')

thanks,

michele

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 3

Michele - Check out the isnumeric function and you should be able to use that to solve your problem.

Glad you were able to find it. I had to deal with feeds from labs and Docs for quite a while and am pretty familiar with the lovely data integrity issues that come along with it. 🙂

Have fun!

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

paytonm Mr or Mrs. 500 Points: 529 More actions · Answer 4

thanks again. I was able to write it with the isnumeric function and got the same result set. It took 3.5 minutes longer to run, but I think it will be better code in the long run.

michele