October 14, 2014 at 10:47 am
One column in the table is varchar(10).
This has values like '6.7' , '3' , 'See Comment' ,'00.45'
How do I add a filter in my where clause that will only give me the numeric ones
I get the error message "Error converting data type varchar to numeric..."
When I run the following query
Select top 100 ABC.observ_value, * from
(
SELECT P.last_name, p.first_name, P.date_of_birth, l.test_desc,
CONVERT(VARCHAR(28), r.coll_date_time, 112) collection_dt ,x.result_desc,
(x.observ_value)
FROM lab_nor l
inner join lab_results_obr_p r on (l.order_num = r.ngn_order_num )
inner join lab_results_obx x on (r.unique_obr_num = x.unique_obr_num )
inner join patient_encounter pe on ( pe.enc_id = l.enc_id )
inner join person p on ( p.person_id = pe.person_id )
WHERE
-- x.result_desc like '%HDL%'
--AND r.person_id = 'replace with person id '
--AND
l.test_status = 'Final'
AND l.delete_ind = 'N'
---AND r.enterprise_id = '000002'
AND r.practice_id = '0038'
AND CONVERT(VARCHAR(28), r.coll_date_time, 112) <= '20141020'
AND
x.result_desc like 'H%'
and
x.result_desc like '%A1c%'
and
ISNUMERIC(x.observ_value)=1
)ABC
WHERE
CAST( ABC.observ_value as decimal(19,2) ) > 8.0
October 14, 2014 at 11:09 am
ISNUMERIC doesn't necessarily mean it will convert successfully to a decimal.
SELECT ISNUMERIC('$123.5')
SELECT CAST('$123.5' as DECIMAL(19,2))
October 14, 2014 at 11:11 am
This is one of the reasons to use the appropriate data types for your columns. This validation might not work if you have 2 or more points in your string.
I changed your dates manipulation.
SELECT P.last_name
,p.first_name
,P.date_of_birth
,l.test_desc
,CONVERT(CHAR(8), r.coll_date_time, 112) collection_dt --use only the needed length
,x.result_desc
,x.observ_value
FROM lab_nor l
INNER JOIN lab_results_obr_p r ON (l.order_num = r.ngn_order_num)
INNER JOIN lab_results_obx x ON (r.unique_obr_num = x.unique_obr_num)
INNER JOIN patient_encounter pe ON (pe.enc_id = l.enc_id)
INNER JOIN person p ON (p.person_id = pe.person_id)
WHERE l.test_status = 'Final'
AND l.delete_ind = 'N'
AND r.practice_id = '0038'
AND r.coll_date_time < '20141021' --This will be converted to a date
AND x.result_desc LIKE 'H%'
AND x.result_desc LIKE '%A1c%'
AND CASE
WHEN ABC.observ_value NOT LIKE '%[^0-9.]%' --allow only numbers and decimal point
--EDIT: Added aditional validation. Note that all this will affect performance.
AND LEN(ABC.observ_value) - LEN(REPLACE( ABC.observ_value, '.', '')) < 2
THEN CAST(ABC.observ_value AS DECIMAL(19, 2))
ELSE 0
END > 8.0
October 14, 2014 at 11:11 am
As ZZartin has said, ISNUMERIC() = 1 doesn't guarantee that the value can be CAST to a NUMERIC type. You'd think that would be how it would work, but it isn't. I'd explain further but Jeff Moden has already done it in this article, http://www.sqlservercentral.com/articles/ISNUMERIC()/71512/. Give that a read and if it doesn't help you post back here.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
October 14, 2014 at 3:09 pm
I suggest a different method to avoid 2 or more decimal points:
AND CASE
WHEN ABC.observ_value NOT LIKE '%[^0-9.]%' --allow only numbers and decimal point
--EDIT: Added aditional validation. Note that all this will affect performance.
AND LEN(ABC.observ_value) - LEN(REPLACE( ABC.observ_value, '.', '')) < 2
AND ABC.observ_value NOT LIKE '%.%.%'
THEN CAST(ABC.observ_value AS DECIMAL(19, 2))
ELSE 0
END > 8.0
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
October 14, 2014 at 4:02 pm
ScottPletcher (10/14/2014)
I suggest a different method to avoid 2 or more decimal points:
AND CASE
WHEN ABC.observ_value NOT LIKE '%[^0-9.]%' --allow only numbers and decimal point
--EDIT: Added aditional validation. Note that all this will affect performance.
AND LEN(ABC.observ_value) - LEN(REPLACE( ABC.observ_value, '.', '')) < 2
AND ABC.observ_value NOT LIKE '%.%.%'
THEN CAST(ABC.observ_value AS DECIMAL(19, 2))
ELSE 0
END > 8.0
+1
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy